2

I've been working to try and connect an ASP.NET application to an IIS Site running in an App Pool using Integrated Security. I was eventually unsuccessful in that attempt until I made the Login I'd created to represent the App Pool a Sysadmin, which I understand is not a good thing to leave in there. It's on localhost, but it still makes me uneasy.

So I decided to try to use a Login/Password combo instead of Integrated Security. To that end, I created a new Login in my SQL Server Management Studio: My newly created Login

And told it to use a password, making sure that I know what the password and username are.

  • My Default database is master
  • Server Roles is only 'public'
  • In User Mapping I added db_owner to one of my databases, and all the others are only 'public'
  • In Securables, 'Connect SQL' is Granted to the SQLEXPRESS server

I then disconnected from SSMS and tried to reconnect using my newly-created Login.

Logging in with my new Login

It didn't allow me to log in:

Nope, denied.

According to: https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-login

After creating a login, the login can connect to SQL Server, but does not necessarily have sufficient permission to perform any useful work.

However, that appears to not be the case in this instance. What am I doing wrong?

I did make sure that my Server Authentication was set correctly, per SQL Server 2008 can't login with newly created user

Another data point: when I try (and fail) to log in 10 times in rapid succession and then go back in with Windows authentication, the 'Login is locked out' notification in the Status tab is not checked.

Kyle Humfeld
  • 1,887
  • 4
  • 23
  • 28
  • 1
    Check SQL Server **error log** to find out the reason of failure. The next row to 18456 error says in clear what it was caused by – sepupic Aug 23 '17 at 09:21
  • Good point! It says that 'Server is configured for Windows authentication only. [CLIENT: ]', but when I look at the Server properties -> security, the 'SQL Server and Windows Authentication Mode' radio button is selected. I even restarted SSMS after changing it to that, but no luck. Any idea why the server is apparently ignoring that setting? – Kyle Humfeld Aug 23 '17 at 17:59
  • GOT IT! I needed to restart the SQLEXPRESS Service in Windows, rather than just restarting SSMS. Thanks or your help! – Kyle Humfeld Aug 23 '17 at 18:03

1 Answers1

1

The thing I was missing was going to Windows Services, and restarting SQLEXPRESS to get the change to 'SQL Server and Windows Authentication Mode' to take effect.

After I did that, I was able to log in with my testuser.

Kyle Humfeld
  • 1,887
  • 4
  • 23
  • 28