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?
Best Answer
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?