Yep - you read that right! I want to make my web-application NOT connect to the local SQL Server Express (2008R) database!
I created a domain user account for my web-application, and I added that user to a domain (AD) group.
In my local SQL Server Instance, I created a login for that AD group. The user account is mentioned absolutely nowhere in the sql database.
Then in my local IIS, I changed the app-pool for that site to use the account I just created.
When I browse my local site in my browser, Voila! It connects to the database.
Okay, so I go into AD and remove that user account from the AD group. I do an iisreset. Now I browse my local site in my browsser, Voila!? It connects to the database.
Huh? How is it connecting?
By using [Integrated Security=SSPI][2], the authentication ought to be made via the account being used by the application pool. So, since it doesn't belong to the group that has access to the sql database, then shouldn't it fail?
How can I make it fail? Maybe it is connecting by some other windows authentication? Okay, only logins for my server's instance are:
- mydomaingroup (without my app-pool's user account)
- mydomainadminaccount
- sa
- NT AUTHORITY\NETWORK SERVICE (necessary for everything, even the sql log viewer)
AND - I can confirm that NONE of those logins are user-mapped to the users allowed for my database except for the special domain group.
Ah, it's worse. If the connection string uses SSPI, then it is supposed to accept and use a username and password if present. So if I specify the username and password of the account that I have now left OUT of the AD group, then shouldn't the application pool be trying to login with bad credentials? -- you would think so, but, again, Voila! It connects!
Thus, these two connection strings both connect with a domain user account that seemingly has no credentials in the sql server instance:
<add name="development" connectionString="server=mycomputer\sqlexpress;database=shoppe;Persist Security Info=False;Integrated Security=SSPI; user id=myuser; password=mypassword; Pooling=True" providerName="System.Data.SqlClient"/>
<add name="development" connectionString="server=mycomputer\sqlexpress;database=shoppe;Persist Security Info=False;Integrated Security=SSPI; Pooling=True" providerName="System.Data.SqlClient"/>
I even tried adding Trusted_Connection=False; and it still connects!
So, where is it getting access to connect? Is there not a way to tell which account is being used to connect?
On the other hand, if I remove Integrated Security=SSPI; from the connection string, then I can't connect at all with windows authentication. I don't know if this is a clue or not.
