Help with Creating ODBC Connector using SQL Server Authentication

I am trying to setup an ODBC connection.  The server is Windows Server 2012 running SQL 2014.  The client machine is a Windows 8 64-bit PC.  

On the client, I go into the SysWOW64 folder and run the odbcad32.exe as an administrator.  When selecting the authentication method, if I choose WIndows NT authentication I have no option of typing a username or password.  Therefore, I am under the impression that it is using the Administrator account that I used when I launched odbcad32.exe.  Using the Windows NT authentication mode, I am able to connect to the database.  

If I select, SQL Server authentication using the same Administrator credentials that I used to launch odbcad32.exe then the connection fails.  I receive  the following error:

Connection Failed:
SQLState: '28000'
SQL Server Error: 18456

I would like to use this method as I want to connect with a user that has limited rights to a database.  I double checked my SQL server and it is configured as "SQL Server and Windows Authentication Mode."

Any help is appreciated!
csimmons1324IT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
SysWOW64 means that you are trying to setup a 32-bit connection..

Then, why do you run odbcad32 as admin? There is no need to.

The credentials for the Administrator account are Windows credentials. SQL Server authentication is a separate system. It has no access to those credentials.

And last but not least:

I would like to use this method as I want to connect with a user that has limited rights to a database.  I double checked my SQL server and it is configured as "SQL Server and Windows Authentication Mode."

What is your concrete problem? Can you give use more information, please.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You very certainly do not want to give the ODBC connection admin privileges. Create a user for ODBC in SQL Server with the appropriate privileges, and then provide that user's credentials in the ODBC config - either as user DSN when logged in as that Windows user, if you want to have only that user having access, or in the machine DSN list for general access.
Vitor MontalvãoMSSQL Senior EngineerCommented:
csimmons1324, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.