Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server DSN Configuration Question??

Posted on 2008-10-07
5
Medium Priority
?
648 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

926 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