locked
Connecting through ODBC from other machines RRS feed

  • Question

  • 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.

    Monday, September 21, 2015 6:30 AM

Answers

  • Didn't I say "review"? :-) Anyway, 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

    will restore the server to the default configuration. Change public to specific login or server role if you prefer.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Rishdin Ameer Monday, September 21, 2015 8:47 AM
    Monday, September 21, 2015 8:35 AM

All replies

  • 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.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, September 21, 2015 7:22 AM
  • class	class_desc	major_id	minor_id	grantee_principal_id	grantor_principal_id	type	permission_name	state	state_desc
    105	ENDPOINT	2	0	2	1	CO  	CONNECT	G	GRANT
    105	ENDPOINT	3	0	2	1	CO  	CONNECT	G	GRANT
    105	ENDPOINT	5	0	2	1	CO  	CONNECT	G	GRANT
    105	ENDPOINT	65537	0	260	259	CO  	CONNECT	G	GRANT
    105	ENDPOINT	65537	0	261	259	CO  	CONNECT	G	GRANT
    105	ENDPOINT	65537	0	269	259	CO  	CONNECT	G	GRANT
    105	ENDPOINT	65537	0	270	259	CO  	CONNECT	G	GRANT
    name	endpoint_id	principal_id	protocol	protocol_desc	type	type_desc	state	state_desc	is_admin_endpoint
    Dedicated Admin Connection	1	1	2	TCP	2	TSQL	0	STARTED	1
    TSQL Local Machine	2	1	4	SHARED_MEMORY	2	TSQL	0	STARTED	0
    TSQL Named Pipes	3	1	3	NAMED_PIPES	2	TSQL	0	STARTED	0
    TSQL Default TCP	4	1	2	TCP	2	TSQL	0	STARTED	0
    TSQL Default VIA	5	1	5	VIA	2	TSQL	0	STARTED	0
    MirroringAX	65537	259	2	TCP	4	DATABASE_MIRRORING	0	STARTED	0

    Hi Erland,

    Thanks for your assistance. Above are the results of the two queries. 



    • Edited by Rishdin Ameer Monday, September 21, 2015 8:20 AM slight addition
    Monday, September 21, 2015 7:47 AM
  • Didn't I say "review"? :-) Anyway, 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

    will restore the server to the default configuration. Change public to specific login or server role if you prefer.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Rishdin Ameer Monday, September 21, 2015 8:47 AM
    Monday, September 21, 2015 8:35 AM
  • Thank you very much Erland. 

    I was not sure what to do since I had not done anything with endpoints before. hence posted the results here.

    I got this from one of your other posts and it worked.

    Thank you very much once again. 

    Regards,

    Rishdin. 

    Monday, September 21, 2015 8:49 AM
  • Yes, this is not an easy one to solve unless you have seen it before. (Which  I have, thanks to the forums). Your description that it worked when you were sysadmin was very helpful.

    What I have not been able to understand is why this permission disappears. That is, is this because someone revokes this permission consciously? Is it because it is recommended in some security-lockdown paper? Or is a piece of software that revokes the permission behind your back?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, September 21, 2015 10:27 AM
  • I am not sure how it happened. 

    We have two instances of SQL 2012 on the same machine. and databases on both of them are mirrored. 

    I remember there were some issue setting up mirroring for both the instances. The consultant doing so, mentioned that endpoints have to be created to do this. 

    May be he tried out something and resulted in this. 

    Anyhow, nice to get some new info. 

    Regards,

    Rishdin. 

    Monday, September 21, 2015 10:34 AM