3

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.

  • 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
7

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.

  • 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
2

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.

  • Hi Adam - are you saying that after you utilised the DSN for a Linked Table, that Access went and edited the registry setting to store the password? That doesn't sound right. Surely Access is just storing the password in the connection information for the linked table, and the DSN is not being changed. – Mike Jun 7 '16 at 0:03
1

Using PowerShell like the following I was able to create a DSN with stored credentials for a Sybase SQL Anywhere Database:

$PropertyValue = @"
ServerName=<ServerName>
Integrated=NO
Host=<HostName>
DatabaseName=<DatabaseName>
"@ -split "`r`n"

$Credential = Get-Credential

Add-OdbcDsn -Name <DSNName> -DriverName "SQL Anywhere 12" -SetPropertyValue $PropertyValue -Platform 32-bit -DsnType System
New-ItemProperty -Path HKLM:\SOFTWARE\WOW6432Node\ODBC\ODBC.INI\<DSNName> -PropertyType String -Name UID -Value $Credential.UserName
New-ItemProperty -Path HKLM:\SOFTWARE\WOW6432Node\ODBC\ODBC.INI\<DSNName> -PropertyType String -Name PWD -Value $Credential.GetNetworkCredential().password

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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