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

asp.netpermissionssql-server-2008

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?

Best Answer

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).

enter image description here

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.

Related Topic