SQL Windows authentication for same name accounts
Posted on 2011-09-06
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  [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.)