Solved

SQL Server DSN Configuration Question??

Posted on 2008-10-07
5
641 Views
Last Modified: 2012-06-21
I have a Access/VBA front-end application that will connect to a SQL Server back-end database and link several tables using a DSN.  I need to store the User ID and Password in the DSN such that the front-end Visual Basic code to link the tables will be structured as shown below ...

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=DynamicsGP", acTable, "dbo.rm00101", "RM00101"

Currently, I get prompted for the password each time and for each table.  In my DSN when prompted ...

"How should  SQL Server verify the authenticity of the Login ID'?  

1. With Integrated Windows Authentication.
2. With SQL Server Authentication using Login ID and Password entere by user.

I have #2 selected and entered a Login ID with Password but the Password never remains saved thus I get prompted each time in the VB Code to link the tables using this DSN.

Any help will be appreciated.

Thanks,

ET
0
Comment
Question by:Eric Sherman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 22664181
If you can use Windows Authentication, by all means do so.  Life is so much simpler then.  

With SQL Authentication, the userid/password must be stored somewhere.  Since you cannot store them in the DSN (since they are then be visible in the registry or in a text file), you need to supply the userid/password from your application.   (For example, you can add userid and password parameters to the connection string portion of the transfer database command .

It's doable, but more work, more issues, and less secure than Windows Authentication.  
0
 
LVL 19

Author Comment

by:Eric Sherman
ID: 22664373
Thanks for the quick reply dqmq ...

If I understand correctly, using SQL Server Authentication, I would have to structure the code as follows ... and it will work.  The drawback is I have to store and retrieve the user id and password from somewhere which is not so secure.

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=Dynamics GP;UID=????;PWD=?????", acTable, "dbo.rm00101", "RM00101"


If I change the DSN to Windows Authentication, I was getting an error that had something to do with "Trust Server Certificate" or setting it to No in the DSN.  After switching back and forth it seemed to have went away but I was getting that at first.

Any ideas ...

ET

0
 
LVL 19

Author Closing Comment

by:Eric Sherman
ID: 31504020
Thanks for your input and help!!!
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22665248
You are correct about UID=,PWD= in the connection string.

Windows authentication is generally the preferred way to connect, provided your windows logins are reasonbly secure.  Then you can dispense with userid/password in the connection string and the DSN.  You also gain the benefit of managing permissions at the group level.  It's truely a much simpler, yet more powerful, approach to security.  If you have the option, I strongly urge you back up a step and get it working.  

If you cannot connect with windows authentication, please post the exact error message and we can help.  It's undoubtedly something simple.  
0
 
LVL 19

Author Comment

by:Eric Sherman
ID: 22665507
Ok, thanks again for the comments dqmq and you answered my question, your solution worked.  

Using windows authentication works and the error I was referring to in my last post was from something else.  The accounting system also connects to SQL Server and it must connect using SQL Server Authentication (that's they way their front end was designed).  I was using that same Accounting Sys DSN to test my connections from the Access/VBA application and changing it to Windows Authentication in there caused that error to be generated when I wanted to open the Accounting front-end.  I changed it back to SQL Server Authentication then created a separate DSN for my Access/VBA application that will use Windows Authentication and it work perfectly.  All is ok now.

Thanks again ...

ET
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question