Exception Details: System.Data.SqlClient.SqlException: Login failed for user

Question

One of our user from another domain WATSON received the following error message:

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'WATSON\User1'.

when he is trying to access one of our web application that retrieve information from a database.

The web application and the database that the web application use is located in a Windows Server 2008 R2. The machine name is Jack11 and has join the domain WATSON. The web application is hosted in the IIS wile the database is hosted in the SQL Server 2008 Enterprise.

Do I need to setup my SQL Server 2008 Enterprise in order to allow domain user such as in this case WATSON\User1 (not local user) to access web application that uses any of the database inside the SQL Server 2008?

(Kindly note that there might be more than 50 domain users that may required to access the web application and therefore it would be inefficient to add each domain user to the database's user rights)

Answer 1

One of our user from another domain (...)

To authenticate against a resource in another domain, either there must be a domain trust established (ask your Windows sysadmins), or pass-through authentication must be used (very likely not in this case). This is true for Windows in general, not just SQL Server.

Do I need to setup my SQL Server 2008 Enterprise in order to allow domain user (...)

If there isn't an existing Windows user or group login for the user, then yes, you'll need to create a new login.

Kindly note that there might be more than 50 domain users that may required to access the web application and therefore it would be inefficient to add each domain user to the database's user rights

I agree -- with this type of scenario, I would recommend setting up a group in AD, and then creating a Windows group login in SQL Server. This way, access is controlled by AD group membership, and you don't have to mess around with changing logins in SQL Server once it's set up.

Answer 2

SQL Server 2008 always supports Windows authentication, either local or domain. However, individual users need to be granted access. In SSMS connect to the instance and expect Security then Logins. If the user or a group that they're in is listed they they already have access to the server and there's another issue. A more detailed error message with a state code will be found in the SQL log. The following page lists out the different states for login failed errors and what they mean: http://msdn.microsoft.com/en-us/library/ms366351%28v=SQL.100%29.aspx

If the user needs to be granted access right click on "Logins" then fill out the dialog that pops up. Make sure to grant them access to the objects they need via a server role, a db role under "User Mapping", or to specific objects after the login has been created.