1

how to connect to MS SQL from IIS when both are on different machines.

MS SQL is configured to windows authentication.

I'm getting:

Login failed for user ''. The user is not associated with a trusted SQL Server connection. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user ''. The user is not associated with a trusted SQL Server connection.

How to setup authentication properly?

4 Answers 4

3

Whom do you whish to authenticate to the SQL Server? The IIS application pool identity, or the web client user identity? In other words, do you impersonate in IIS/ASP? And when you say 'IIS', you do mean some form of ASP/ASP.Net process/app pool, right?

If you don't impersonate then SQL will authenticate your app pool identity. If your app pool is running as a domain user it will be that domain suer. If the app pool is running as network service or LocalSystem then it will be the host identity (domain\hostname$ account). If the app pool is running as a local user or a local service then it is not supported and you must use one supported identity.

If you impersonate, then you are actually delegating the client user identity and for this you are required to enable the IIS/ASP for costrained delegation. There is plenty of literature how to do this:

Of course, both the IIS and the SQL Server should be meberes of domains with an existing trust relationship between them, otherwise authentication is not possible (the identical user/password 'trick' is a horrible hack).

Once you figure out whom exactly the authentication shoudl result into, you can take the steps to allow authorization inside SQL Server, which are the ususal steps (create a login, grant appropiate rights, create an user, grant appropiate rights).

As a side note, the code should not let exceptions go unhandled. Perhaps is time for ELMAH?

1
  • >>> And when you say 'IIS', you do mean some form of ASP/ASP.Net process/app pool, right? -- Yes.
    – user34402
    Feb 11, 2010 at 8:27
1

Depending on the version of SQL Server you may have missed out on enabling remote connections this time round: http://support.microsoft.com/kb/914277.

Also, rather than testing with your app you should test with Management Studio or Management Studio Express. Your app is at the moment just adding another variable factor into the equation, so to begin with we need to establish that you can successfully connect from a remote machine using something that is known-good. Once that's confirmed we can bring IIS back into things and tackle it from that side.

0

I think that it depends on what language you've written your web application in. Does it have any documentation on data-access methods, like ADO or something similar?

1
  • I successfully used IIS and MSSQL on my previous servers. After I moved to new servers it doesn't work. There should be windows authentication settings in MS SQL and IIS i just don't know how to setup them to make it work.
    – user34402
    Feb 10, 2010 at 21:56
0

I created a user on both machines with the same name. Set up IIS pool to run by that user and assigned user to SQL group on another server.

Helped so far.

1
  • Yes, of course the username and password that you're using has to be the same on both machines.
    – mfinni
    Feb 11, 2010 at 19:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .