sql 2012: Error connecting thru odbc from other machines

• Rishdin Ameer-354721

  SSC Enthusiast

  Points: 137

  More actions

  September 21, 2015 at 1:00 am

  #304751

  Hi,

  I have an SQL2012\instance running.

  I create a sql user who is part of sysadmin, securityadmin, setupadmin and serveradmin roles.

  When I try to connect through odbc using this user from other machines, it works fine. But if I remove it from sysadmin, I get an error message

  Connection Failed:

  SQLState : '28000'

  SQL Server Error: 18456

  [Microsoft][SQL Server Native Client 11.0][SQL Server][Login Failed for user:user1]

  Any help is appreciated.

  Regards,

  Rishdin.

• ARC211

  Ten Centuries

  Points: 1283

  More actions

  September 21, 2015 at 8:23 am

  #1828094

  Does the user you are trying to use have Grant (Connect SQL) to the default DB for that user.

• ARC211

  Ten Centuries

  Points: 1283

  More actions

  September 21, 2015 at 8:27 am

  #1828095

  Also, ensure the user is mapped to the DB.

• Rishdin Ameer-354721

  SSC Enthusiast

  Points: 137

  More actions

  September 21, 2015 at 11:50 pm

  #1828276

  thanks ARC211. this problem is solved.

  below is the solution I got from technet forums

  It seems that the user does not have CONNECT permission on the TCP endpoint (which is granted to public by default).

  Review the output of these two SELECT statements:

  select *from sys.server_permissions where class_desc = 'ENDPOINT'

  select *from sys.endpoints

  There should be a line for the TCP endpoint in the first output with grantee_principal_id = 2 (public), and there should be no DENY lines.

  I sent them the results of the above queries and they replied as below, which solved the problem.

  You can see that TSQL Default TCP has id 4, but this endpoint is not present in sys.server_permissions.

  GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO public