SQL 2014 connection from an ODBC client | keeps requiring credentials.


I have a Windows 7 Professional workstation.

I am running Crystal Reports on it and need to connect to a SQL 2014 database.

I was able to create an ODBC connection via Data Sources (ODBC) in Control Panel.  Connectivity works fine.  However I am prompted for credentials.  I am able to connect successfully when I enter the database SA password.  However, I would like the connection to take place either saving the SA password so it does not have to be entered; or using an Active Directory user account (which is okay for user to have to enter).

I'm hoping I'm explaining this adequately.  

Can you offer advice?

Thanks in advance.

Who is Participating?

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

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.

Lawrence TsePrinciple ConsultantCommented:
First of all using SA password to access SQL Server is not a recommended approach but that works if security is not a concern.  Please note by default SQL Server have SA account disabled and SQL Server Authentication disabled.   By default SQL Server use Windows Authentication.  Please verify which authentication mechanism you are using first.

I wish your SQL Server SA password and Windows Administrator password and AD Domain Administrator password are not all identical else it will be difficult for you to tell which password you are entering, and which authentication mechanism you are using without looking at the configuration carefully.

Once you have verify your authentication mechanism, you can go back to your Control Panel ODBC section.  What DNS you are creating?  User, System or File DSN?  Except doing file DSN, you will have a chance to select SQL Server as the driver (fig.1) and type input SQL server detail (fig.2).  

When you proceed, you will be asked for authentication mechanism (fig.3).  If you choose to use Windows Authentication, no user name and password will be required, since SQL Client will by default use your currently logged in account and credential to authenticate with SQL Server and you will have no control on this.  If you choose to use SQL Server Authentication, you can enter a SQL Server login name and type in your password.  

If you use File DSN, you will need to hard-code your SQL Server login name and password by clicking the "Advanced..." button.  Please check fig.4.


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
realtimerAuthor Commented:
Very helpful.  Thank you!
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

From novice to tech pro — start learning today.