Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.