Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Why does only one user have access? Expand / Collapse
Author
Message
Posted Tuesday, December 4, 2012 9:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 10:56 AM
Points: 30, Visits: 89
If a coworker gets a new computer, they can't access our database from any other user account on the computer other than one called 'ctap' and I can't figure out why.

We work with two servers, the one running SQL Server (tulip) and the one running our Access front-end (panda). Our data is stored as SQL Server tables on tulip, but all data manipulation and data entry occurs from the Access program on panda, so we're working with linked tables over an ODBC connection. In the past when I've tried to add new users I get an error message saying "Login failed for user (null). Not a trusted connection." But there's a workaround that I just figured out. If a create a user account on each client called 'ctap' with the same password, I can make the connection to the database just fine. On both servers, users are placed in a group called CTAP_Professionals, presumably because one of the users on SQL Server is labeled tulip\CTAP_professionals (the user 'ctap' is also part of this group). I can't figure out why this one user has access, but no other users can get access, even though their profiles are the same!

Post #1392584
Posted Tuesday, December 4, 2012 10:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 11:46 PM
Points: 42, Visits: 290
One reason could be that other users are not part of CTAP_professional group and only this group has access to the database.

However, the most probable reason could be that the client application is configured to connect to the database using only 1 userid and password and nothing else.
Post #1392607
Posted Tuesday, December 4, 2012 11:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 10:56 AM
Points: 30, Visits: 89
The first part of your reply doesn't hold because all of the users I've tried to connect are part of the CTAP_Professionals group on the server.

How would I go about checking the second part of your reply to see if that's the problem, and how would I go about fixing that?
Post #1392628
Posted Wednesday, December 5, 2012 8:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:30 AM
Points: 1,949, Visits: 2,122
ODBC settings are on each client computer. Go to Control Panel / Administrative Tools, and there should be an option called Data Sources (ODBC). From there, you should be able to find the DSN that the client program is using, and looking at the DSN (I believe it's the configure button) will tell you how the client is connecting to the server, what user it is, etc.

If it's using Windows Authentication, then the connection to the database is dependent on what network user is logged into the client computer. You would have to verify in Management Studio that there is a SQL Server login setup for that network user, and that login has a user with privileges in the specific database.
Post #1393055
Posted Wednesday, December 5, 2012 9:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 10:56 AM
Points: 30, Visits: 89
We're using Windows authentication and I've tried all sorts of permutations for setting up an ODBC connection. In management studio there is a user listed (with permissions to our databases) labeled as tulip\CTAP_Professionals. There's no separate user for tulip\ctap. I was assuming the having membership in the group CTAP_Professionals on tulip would give users permission to connect to the database, but apparently the only member of that group able to gain access is the general user "ctap".
Post #1393064
Posted Wednesday, December 5, 2012 10:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:30 AM
Points: 1,949, Visits: 2,122
ryetimothy (12/5/2012)
We're using Windows authentication...user listed (with permissions to our databases) labeled as tulip\CTAP_Professionals. There's no separate user for tulip\ctap. I was assuming the having membership in the group CTAP_Professionals on tulip would give users permission to connect to the database...

So tulip is your SQL Server server and your domain controller? If tulip is not a domain controller, then the users and groups setup on it (tulip\CTAP_Professionals) only have a security context within that server, not from the client computers. I'd talk with your network administrator and have the CTAP_Professionals group created in Active Directory in the domain controller, then create the login in SQL Server based on the DC's CTAP_Professionals group instead of the tulip\CTAP_Professionals group.
Post #1393126
Posted Wednesday, December 5, 2012 11:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 10:56 AM
Points: 30, Visits: 89

So tulip is your SQL Server server and your domain controller? If tulip is not a domain controller, then the users and groups setup on it (tulip\CTAP_Professionals) only have a security context within that server, not from the client computers. I'd talk with your network administrator and have the CTAP_Professionals group created in Active Directory in the domain controller, then create the login in SQL Server based on the DC's CTAP_Professionals group instead of the tulip\CTAP_Professionals group.


Not entirely sure how it all works. The Access database files are on a separate server (panda) that also has a group called CTAP_Professionals. Not sure if that is relevant or not. I'll see if my network administrator can shed any light on the issue of whether tulip is a domain controller or not.
Post #1393147
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse