SQL Server 2008: Getting Login failed for user "Domain\User". Failed to open the explicitly specified database [CLIENT: IP.ADD.RR.ESS]
- by GodEater
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?
