0

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?

Community
  • 1
GodEater
  • 540
  • 1
  • 6
  • 12

3 Answers3

0

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.

Justin Helgerson
  • 978
  • 7
  • 12
  • Hi, sorry - yes, I should have mentioned I checked that too - and it does indeed appear under 'Logins' exactly where you indicate above. Alas, the ASP.NET is a freebie download from Microsoft, and so I can't modify the code. I've tried fiddling the account that Application Pool runs as, unfortunately this just gives me a 503 error in the web browser, and my general lack of IIS know-how prevents me from further fiddling there. – GodEater Mar 07 '12 at 15:48
0

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.

Mindy
  • 117
  • 2
  • 2
  • 8
0

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.

GodEater
  • 540
  • 1
  • 6
  • 12