Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a SQL Server where admin users can connect locally and remotely, but read only users can only connect locally and get a "Login failed" error when connecting remotely. It's using SQL Server Authentication. Any ideas on what could be happening?

share|improve this question
    
Further update, the connection only works when i give a user CONTROL DATABASE securables but does not work otherwise. –  Jorge Guzman Apr 29 '11 at 14:33
add comment

2 Answers 2

up vote 1 down vote accepted

Dynamo's advice is good - if you look at the SQL Server logs for SQL Server 2005 or later, and failed logins are audited, it will give you a state code. If you look up the code, it will give you a better idea of why.

MSDN Login Failed: http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

http://www.dbguru.co.uk/sql-server/post/2011/03/29/Error-18456-Understanding-login-failed-error-messages-in-SQL-Server.aspx

share|improve this answer
    
Thanks it's state 12 "Valid login but server access failure" which makes it a bit more puzzling :) Any idea? –  Jorge Guzman Apr 29 '11 at 12:47
    
The same user then works again when I assign sysadmin –  Jorge Guzman Apr 29 '11 at 13:13
add comment

It's possible that their default database is not set to a database that they have read access to so they are being denied access as soon as they connect. Make sure that the users have the correct database set to their default and retry the connection.

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.