Cannot get login to work for ODBC driver

I am trying to set up an ODBC driver to SQL Server 2000 and cannot what I am doing wrong. The error I get is sqlstate 28000 and error 18456 failed login.

Here are the steps I took:

create a username under computer management
then in enterprise manager I create login by going to security->login new login
From there I browse to the user I created in computer management and windows nt is selected as well as grant access
then went to database and rolls and gave it read and write permissions for a few databases. The default database is set to master. Everything seems to work just fine up to this point.

Then I go to the ODBC manager and then SysDSN then add and I pick SQL Server. From here I name the DSN, pick the server that has both the windows username and the SQL Server on it and click next.

Then I click SQL Server authentication and enter the username and password, and checked to make sure it was right at least 20 times, and then I get the 18456 error.

Am I missing a step, what happened?

Any help is appreciated.

Solution

You've set up a windows user, not a SQL user, so use Windows authentication in your connection, not SQL User.

This will use whatever you are logged into windows as to connect to the SQL Server. This is 'trusted' authentication, which means you dno't have to remember a zillion passwords, it uses your current one.

If, as I suspect, this is not what you want, then you should create a SQL User on SQL Server, not a windows one. In this case you only create a user name and password in SQL Server, not in Computer Management.

Creation of SQL Users are often disabled by default so you may need to change your server configuration to allow for it.

I always recommend using Windows users not SQL Users, but ifyou are not in an Active Directory environment then SQL Users are fine.