Sql-server – Integrated Security Success on Master, Fails on Other Database

authenticationloginsSecuritysql serversql-server-2005

I'm a bit perplexed as to why my connection string is selectively succeeding on the master database in SQL Server 2005. Here are the details of my app. Any ideas where to look? I'm clearly missing something. Also keep in mind that the IIS Server and SQL Server are on different machines within an Intranet environment where I am attempting to implement Windows Authentication. Thanks. 🙂

Connection strings:

Server=SQLServer;Database=master;Integrated Security=true —succeeds
Server=SQLServer;Database=other;Integrated Security=true —fails

Fail Error Message:

Cannot open database "other" requested by the login. The login failed.
Login failed for user 'DOMAIN\IIS_SERVERNAME$'.

My IIS Server App Pool and Authentication settings:

  • AppPool: .NET 4.0; Integrated Pipeline; Identity=Network Service
  • Authentication: Impersonation=Disabled; Windows Auth=Enabled; Kernel-mode=Enabled; Providers=Negotiate,NTLM

Update: Clearly there is a Login of some sort for the machine connecting to my SQL Server with the Integrated Security, otherwise it wouldn't succeed in connecting to the master database right? I would guess that to mean there's an issue at the database level, but I cannot figure out which user account would give it access to master because there are only a handful of users for it.

Best Answer

In SQL Server, as long as a LOGIN is valid, it will have access to the master database in a public role. This is both for historical and practical reasons. For example, stored procedures beginning with "sp_" are searched first in the master database and will execute from there. The implication is that users need to first have access to the master database to begin with. tempdb is another database that allows user access via the public role.

User databases do not have this as a default setup, so unless the Login is specifically given permission, or has server-wide roles such as "sysadmin", it will not be able to switch to the database context, hence your error.

The fact that you manage to log in at all using the machine account means that it is either explicitly a created LOGIN, or is a member of a group that appears under "Logins" when you browse the Object Explorer in SQL Server Management Studio.

[SERVER\Instance]
    Security
        Logins
             .... here ....

You can specifically assign roles or grant execute permissions to the machine account:

use other;
exec sp_addrolemember db_datareader, [DOMAIN\IIS_SERVERNAME$]
GRANT EXECUTE ON sp_myproc TO [DOMAIN\IIS_SERVERNAME$]
Related Question