SQL Server 2008: Getting Login failed for user "Domain\User". Failed to open the explicitly specified database [CLIENT: IP.ADD.RR.ESS]

Question

This is a very similar issue to " SQL Server 2008 login problem with ASP.NET application: Failed to open the explicitly specified database " which unfortunately seems to have gone unsolved.

My issue here is subtly different. Firstly the account failing login is not 'NT AUTHORITY\NETWORK SERVICE' - it's an actual domain account. Secondly, there are two machines involved - I gathered from the first question it was a single machine running both the IIS and SQL instances.

The application which is trying to connect to the database is an ASP.NET one running on another server (if that makes any different, I'm not sure it does.) The ConnectionString being used in the web.config for the application is :

data source=MySQLServer;initial catalog=MyDatabase;integrated security=sspi;

And the Application Pool is set to NetworkService for Identity.

So - in the web app, I get the following error :

Cannot open database "MyDatabase" requested by the login. The login failed. Login failed for user 'MyDomain\WebServerMachineName$'

In the SQL Server logs I see :

Login failed for user 'MyDomain\WebServerMachineName$'. Reason: Failed to open the explicitly specified database. [CLIENT: Web.Server.IP.Address]

Running this bit of SQL against the database in question :

USE [MyDatabase] GO SELECT SDP.name AS [User Name], SDP.type_desc AS [User Type], UPPER(SDPS.name) AS [Database Role] FROM sys.database_principals SDP INNER JOIN sys.database_role_members SDRM ON SDP.principal_id=SDRM.member_principal_id INNER JOIN sys.database_principals SDPS ON SDRM.role_principal_id = SDPS.principal_id 

Gets me this result :

MyDomain\WebServerMachineName$ WINDOWS_USER DB_DDLADMIN MyDomain\WebServerMachineName$ WINDOWS_USER DB_DATAREADER MyDomain\WebServerMachineName$ WINDOWS_USER DB_DATAWRITER 

Which appears to me to indicate I've got the permissions right.

Anyone have any idea why it's not working, or how I can narrow the issue down some more?

Answer 1

Just because you have DDLADMIN, DATAREADER and DATAWRITER access to the database does not mean that the user actually has access to login to the server.

Could you verify that the account you are running under (in your case the WebServerMachineName) is listed under the security logins (pictured below).

As an aside, I would recommend creating a service account and then impersonating that account in your ASP.NET application. Then you can grant database access to that service account.

Answer 2

Have you checked the user mappings? Right click on the user name in the Security/Logins section shown in the previous post and choose properties to check the User Mapping section to make sure that the user in the specific database instance is mapped to the one in the Security section.

Answer 3

I've basically given up with this now, and resorted to using a SQL Server login instead. Much simpler, although I'm generally not keen on using them in place of windows IDs.