Windows Forum

Ask something
Discussion Locked

Can't connect to SQL Server via ODBC

0 helpful
4 replies

by steves815

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have created a user on a Win2K SBS system using active directory. I have added this user to SQL Server as well and given him access permission to a database. However, when this user is logged in to a Windows 2000 pro workstation and tries to use an application that uses a drop down list based on an ODBC connection to this database he is presented with an SQL Server log in dialog. (ODBC DSN uses Windows authentication.) His ID and password are rejected and he must log in with some other ID. (Pretty annoying when using a drop down!) Other ID's that are set up like his do not experience this problem. Can anyone say what the correct SQL Server permission setup must be? I swear this should not be happening. The error is "SQL Server Error: 18456 Login failed for ". Any clues appreciated. More detail upon request.

4 replies

by kamrulm

What version of sqlanywhere you currently using? Check start command in Start option on ODBC config. For example
C:\sqlany50\win32\dbclient.... SQL Anywhere v.5.5
C:\sqlany\win32\dbclient....for SQL Anywhere v.5.0

by sativocomputerservices

Try looking at your ODBC administration settings. Make sure you have the system DSN and File DSN settings correct and you can connect. The setup asks for an username to use for the connection. This will allow you to make sure you are connecting properly. There is a box that says "Use Windows Authentication". After testing, make sure that box is checked to pass the logon credentials through.

Let me know if this fixes it, if not I have a few other suggestions.

by steves815

The DSN settings are correct for the System DSN. (There is no file DSN.) But, here is what I have found. If the user is created in SQL Server Enterprise Manager by picking from a list of Domain users, the logon is prefaced by the domain name. I.e. for domain "JDC01" and user "Steve" the logon created is "JDC01\Steve" (as a windows user). If there is also a "Steve" user in SQL Server (as a standard user, which was created by an application that uses SQL Server as it's core) the second, longer, logon will be created, but when I try to grant access to the database the dialog responds "Steve already exists..." and no access is granted. If I delete the simple user name I can then grant access to the database and the ODBC connection works. I guess the question now becomes, Do ODBC connections require this type of domain logon name? I have user names that seem to work OK that are not like this, and some that are. The error I got from the ODBC connection was "Log in failed for user JDC01\Steve, SQL Server Error: 18456" which apparently means no access granted. I would appreciate your further comments as this Window versus SQL user name subject seems unnecessarily complicated.

This discussion is closed to new replies.