ODBC DSN for SQL Server via script

29 Apr 2007 2:08 PM

John

Hi

I have the below script to create DSN for SQL Server. The problem is this
does not include a username and password option for SQL Server
authentication. Could someone please guide me how to add the username and
password option for authentication to this script?

Many Thanks

Regards

= Script Below ========================

Const HKEY_LOCAL_MACHINE = &H80000002

strComputer = "."

Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
strComputer & "\root\default:StdRegProv")

strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
strValueName = "My SQL Server"
strValue = "SQL Server"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"

objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath

strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"

strValueName = "Database"
strValue = "My Database"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

strValueName = "Driver"
strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

strValueName = "Server"
strValue = "111.111.111.111"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

strValueName = "Trusted_Connection"
strValue = "No"
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

29 Apr 2007 3:15 PM

Dave Patrick

AFAIK you can't programmatically store the password.

http://support.microsoft.com/kb/184608

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

Show quote

"John" wrote:
> Hi
>
> I have the below script to create DSN for SQL Server. The problem is this
> does not include a username and password option for SQL Server
> authentication. Could someone please guide me how to add the username and
> password option for authentication to this script?
>
> Many Thanks
>
> Regards
>
>
> = Script Below ========================
>
> Const HKEY_LOCAL_MACHINE = &H80000002
>
> strComputer = "."
>
> Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
> strComputer & "\root\default:StdRegProv")
>
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
> strValueName = "My SQL Server"
> strValue = "SQL Server"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
>
> objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath
>
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
>
> strValueName = "Database"
> strValue = "My Database"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>
> strValueName = "Driver"
> strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>
> strValueName = "Server"
> strValue = "111.111.111.111"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>
> strValueName = "Trusted_Connection"
> strValue = "No"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>
>

29 Apr 2007 3:29 PM

Dan Guzman

To add to Dave's response, the SQL Server ODBC driver does not store the SQL
password for security reasons; anyone with access to the DSN could then
connect to SQL Server. The password needs to be provided by the application
when connecting.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"John" <John@nospam.infovis.co.uk> wrote in message
news:%23lObOfmiHHA.688@TK2MSFTNGP03.phx.gbl...
> Hi
>
> I have the below script to create DSN for SQL Server. The problem is this
> does not include a username and password option for SQL Server
> authentication. Could someone please guide me how to add the username and
> password option for authentication to this script?
>
> Many Thanks
>
> Regards
>
>
> = Script Below ========================
>
> Const HKEY_LOCAL_MACHINE = &H80000002
>
> strComputer = "."
>
> Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
> strComputer & "\root\default:StdRegProv")
>
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
> strValueName = "My SQL Server"
> strValue = "SQL Server"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
>
> objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath
>
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
>
> strValueName = "Database"
> strValue = "My Database"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>
> strValueName = "Driver"
> strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>
> strValueName = "Server"
> strValue = "111.111.111.111"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>
> strValueName = "Trusted_Connection"
> strValue = "No"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>
>

3 May 2007 2:05 AM

Aaron Kempf

Dan

crap answers like that is why SQL Server is _STILL_ not secure.
We don't friggin care if you think that it's secure

We don't friggin care what your premise is.
It's goddamn ridiculous that MS refuses to take SQL authentication
seriously.

I GOT CANNED AND BLACKLISTED FROM MICROSOFT FOR COMPLAINING ABOUT SQL
AUTHENTICATION

We just want to create the passwords and use SQL Authentication

I've worked in 100 different SQL Server environments these past 10 years..
and 80% of the clients in the real world use SQL Authentication for
_SOMETHING_.

stfu and take your premise back to MS and tell them to stfu and fix SQL
Authentication
Show quote

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:418F29B3-9C30-406D-80A5-6B4F1AE9758F@microsoft.com...
> To add to Dave's response, the SQL Server ODBC driver does not store the
SQL
> password for security reasons; anyone with access to the DSN could then
> connect to SQL Server. The password needs to be provided by the
application
> when connecting.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "John" <John@nospam.infovis.co.uk> wrote in message
> news:%23lObOfmiHHA.688@TK2MSFTNGP03.phx.gbl...
> > Hi
> >
> > I have the below script to create DSN for SQL Server. The problem is
this
> > does not include a username and password option for SQL Server
> > authentication. Could someone please guide me how to add the username
and
> > password option for authentication to this script?
> >
> > Many Thanks
> >
> > Regards
> >
> >
> > = Script Below ========================
> >
> > Const HKEY_LOCAL_MACHINE = &H80000002
> >
> > strComputer = "."
> >
> > Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
> > strComputer & "\root\default:StdRegProv")
> >
> > strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
> > strValueName = "My SQL Server"
> > strValue = "SQL Server"
> > objReg.SetStringValue

HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

> >
> > strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
> >
> > objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath
> >
> > strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
> >
> > strValueName = "Database"
> > strValue = "My Database"
> > objReg.SetStringValue

HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

> >
> > strValueName = "Driver"
> > strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
> > objReg.SetStringValue

HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

> >
> > strValueName = "Server"
> > strValue = "111.111.111.111"
> > objReg.SetStringValue

HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

> >
> > strValueName = "Trusted_Connection"
> > strValue = "No"
> > objReg.SetStringValue

HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
Show quote

> >
> >
>

3 May 2007 1:51 AM

Aaron Kempf

what are you doing?
SQL linked tables in a MDB?

you should jsut use Access Data Projects, kid

Show quote

"John" <John@nospam.infovis.co.uk> wrote in message
news:%23lObOfmiHHA.688@TK2MSFTNGP03.phx.gbl...
> Hi
>
> I have the below script to create DSN for SQL Server. The problem is this
> does not include a username and password option for SQL Server
> authentication. Could someone please guide me how to add the username and
> password option for authentication to this script?
>
> Many Thanks
>
> Regards
>
>
> = Script Below ========================
>
> Const HKEY_LOCAL_MACHINE = &H80000002
>
> strComputer = "."
>
> Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
> strComputer & "\root\default:StdRegProv")
>
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
> strValueName = "My SQL Server"
> strValue = "SQL Server"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
>
> objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath
>
> strKeyPath = "SOFTWARE\ODBC\ODBC.INI\My SQL Server"
>
> strValueName = "Database"
> strValue = "My Database"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>
> strValueName = "Driver"
> strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>
> strValueName = "Server"
> strValue = "111.111.111.111"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>
> strValueName = "Trusted_Connection"
> strValue = "No"
> objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
>
>