Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server DSN Configuration Question??

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

670 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