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
Solved

SQL Server DSN Configuration Question??

Posted on 2008-10-07
5
631 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

828 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