Solved

SQL Windows authentication for same name accounts

Posted on 2011-09-06
9
359 Views
Last Modified: 2012-08-13
I have a program written in VB.NET that accesses data in a SQL 2000 database.  It uses an ODBC System DSN data source to connect, configured on Windows authentication.  For each user that needs access, I have added domain\user to the Logins in SQL Enterprise Manager and given the user public access to the relevant database.  Everything works as intended.

Where there is an issue is that some users have a SQL login that matches their domain login for the same database.  When I attempt to grant access to the database, it indicates the user already exists.  For example, domain\jsmith has a SQL account jsmith.  I can create domain\jsmith in EM, but I cannot grant them database access as it says user jsmith already exists.

In attempt to remedy the issue, I changed the ODBC System DSN to SQL authentication, specifying a valid SQL login and password.  When I attempt to run the program it gives: "ERROR  [28000] [Microsoft ODBC SQL Server Driver] [SQL Server] Login failed for user '(null)'.  Reason: Not associated with a trusted SQL Server connection."  (For testing, I tried the 'sa' account, and even this doesn't work.)

-Scott
0
Comment
Question by:CC85
  • 4
  • 3
  • 2
9 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 36493252
NO, I would change your odbc back to windows authentication.

Then try giving the user READ access by going to your db and right-click on it.

Select Properties

click on Permissions

Select the name you wish to give permission to and check the appropriate box, in your case SELECT box and I think that should take care of it.

0
 
LVL 1

Author Comment

by:CC85
ID: 36493293
In the database properties permissions tab under user/role, the only users I see are those that already have access to this particular database.  

I'm able to create a login for domain\jsmith but when I go to the Database Access tab of the Login Properties box I cannot give them database access.  It gives: Error 21002: [SQL-DMO]User 'jsmith' already exists.  (I am unable to delete the SQL login for "jsmith" because another software program requires SQL logins for all users.)
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 36493323
You give them permissions through the database as I described.

If you don't see them, then add them by clicking the Add or Search button.

Then click Browse.

There, you see a bunch of users, including the ones you are looking for.

Select it, then look for the appropriate permission as I described like SELEcT
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Accepted Solution

by:
coolfiger earned 500 total points
ID: 36493334
open sql management studio

go to the database iteself expand it
go to security i believe and logins. delete the user thats saying it allready exists and then try re adding .

I would also liek to suggest usign a security group instead of a single user by user ... its a lot easier .. so in active directory u jsut add a user to the group and presto ... access to the odbc..

Please note above you may have to delete the login from under the database logins and under general sql security logins ....

0
 
LVL 8

Expert Comment

by:coolfiger
ID: 36493336
the error you're geting is because the user allready exists on the databse login table ... let me know if my solution works ..
0
 
LVL 1

Author Comment

by:CC85
ID: 36493341
I'm not seeing what you're describing.  In Enterprise Manager, Console Root>MSSQL Servers>SQL Server Group>Server>Databases>MyDatabase.  Right click, Properties, MyDatabase Properties window appears.  Click Permissions.  I see a list of domain\users and SQL logins that already have access.  There is no add or search button.  (There also isn't a SELECT permission, only listed are create, such as Create Table, Create View, Create SP, and Backup.)  I'm using SQL 2000 Standard.
0
 
LVL 1

Author Comment

by:CC85
ID: 36493360
coolfiger - Correct, the user exists as a SQL account using SQL authentication and as domain\user using Windows authentication (two accounts "jsmith" (SQL) and "domain\jsmith" (Windows)).  "Jsmith" with SQL authenticiation is required for another software program, so deleting "jsmith" and leaving only "domain\jsmith" is not an option.
0
 
LVL 1

Author Closing Comment

by:CC85
ID: 36493390
Created a new security group in Active Directory and added the users.  Added domain\ODBC group to SQL logins, granted database access.  Access achieved without adding every user\domain and conflicting with existing accounts.
0
 
LVL 8

Expert Comment

by:coolfiger
ID: 36496635
glad to have helped !
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeā€¦
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question