December 4, 2012 at 9:40 am
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!
December 4, 2012 at 10:20 am
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.
December 4, 2012 at 11:04 am
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?
December 5, 2012 at 8:41 am
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.
December 5, 2012 at 9:00 am
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".
December 5, 2012 at 10:39 am
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.
December 5, 2012 at 11:31 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply