Solved

SQL Server DSN Configuration Question??

Posted on 2008-10-07
5
619 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
  • 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 up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now