Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a legacy CRM that uses a DSN with Windows Authentication to connect to an instance of SQL Server 2005 (part of SBS 2003 Premium). We will soon be doing a major migration leaving us without a Windows Domain - as I understand things, that will mean we need to use an alternative authentication method. We are also planning to migrate from the SQL Server 2005 to SQL Server Express 2008 R2 in the same timeframe.

Unfortunately no modifications to the CRM are possible.

Leaving aside the obvious security issues, is it possible to hard-code a username and password in a system DSN? Alternatively is that another obvious approach to authentication that I am missing?

--EDIT

The DSN is defined in the ODBC Data Source Administrator in the "Administrative Tools" section of the Control Panel on the Windows clients.

--EDIT 2

I gave up trying to do this in the DSN thanks to Jonathan's answer. Fortunately I discovered an .ini file where I could insert a fixed username/password replacing the placeholders - problem solved.

share|improve this question
1  
By hard coded, do you mean that you have access to where the DSN is defined in the CRM, and want to know if you can replace that with a DSN that has user/pass in it? –  Bryan Agee May 25 '11 at 0:35
    
Yes, we have full access to the PCs where the DSNs are defined (not in the CRM - the CRM just knows the name of the DSN) –  Jack Douglas May 25 '11 at 6:16

2 Answers 2

up vote 6 down vote accepted

A System DSN is stored in the registry and does not have the ability to store a password. If you want to store the password, you'd have to use a File DSN and that would require changing the CRM to use the file DSN which you say is not possible. Usually applications that use a System DSN, rely on SQL Server authentication of the users for their security. When the application opens it should prompt the user to login, and this is the username and password that is used with the System DSN information to connect to the database server. If the DSN uses Windows authentication and you are eliminating your Windows Domain, you are stuck. You would have to change the CRM to make it work a different way.

share|improve this answer
    
Thanks for the answer - I tried a bit harder the change the CRM instead of wasting any more time trying to do this in the DSN :) –  Jack Douglas May 25 '11 at 18:32

DSNs vary by provider. I just hardcoded a password for a Microsoft Oracle ODBC for a System DSN. The situation: MS Access 2010 does not save password for Linked Table

\HKLM\Software\ODBC\ODBC.INI{name of DSN}\PWD

After edit, Access no longer prompted for a password when the data source was first accessed.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.