ODBC DSN for SQL Server via script

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,strValueAFAIK 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
"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
>|||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
>|||what are you doing?
SQL linked tables in a MDB?
you should jsut use Access Data Projects, kid
"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
>|||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
"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
> >
> >
>

ODBC connection prompting for 'sa' password

We use Pivot Tables in Excel to provide users with reports. The Pivot Tables connect to SQL Server via an ODBC connection. In the ODBC connection, SQL Server authentication is specified and the 'sa' user name and password are entered. In prior versions
of Excel, the password would be saved in the Pivot Table. However, in Excel 2003 the user is prompted to enter the 'sa' password in order for the pivot table to update. Is there a way to have Excel 2003 pivot tables function as they did in prior versio
ns so that the password in the ODBC connection does not have to be re-entered?
Don't know much about Excel. But can you create another account (not sa)
to access the DB? This way you can keep your sa password secret.
Eric
Mike wrote:

> We use Pivot Tables in Excel to provide users with reports. The Pivot Tables connect to SQL Server via an ODBC connection. In the ODBC connection, SQL Server authentication is specified and the 'sa' user name and password are entered. In prior versio
ns of Excel, the password would be saved in the Pivot Table. However, in Excel 2003 the user is prompted to enter the 'sa' password in order for the pivot table to update. Is there a way to have Excel 2003 pivot tables function as they did in prior vers
ions so that the password in the ODBC connection does not have to be re-entered?
Eric Li
SQL DBA
MCDBA

ODBC Connection Failure

Hi,
I'm trying to create a ODBC connection to SQL server 2000 using SQL server authentication. I have login id "lucky9797" and password "tool" but I'm keep getting the following error message:
Connection Failed:
SQLState:'28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login Failed for user "lucky9797"
Could someone please tell me why am I getting this error message? Thanks.
Message posted via http://www.sqlmonster.com
1) It might be that the collation sequence indicated at installation time is
case insensitive. Try typing "TOOL" instead of "tool"
"syed rahman via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:0e43caa46e9040489d8c88e56a57a606@.SQLMonster.c om...
> Hi,
> I'm trying to create a ODBC connection to SQL server 2000 using SQL server
authentication. I have login id "lucky9797" and password "tool" but I'm keep
getting the following error message:
> Connection Failed:
> SQLState:'28000'
> SQL Server Error: 18456
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login Failed for
user "lucky9797"
> Could someone please tell me why am I getting this error message? Thanks.
> --
> Message posted via http://www.sqlmonster.com

ODBC Connection Failure

Hi,
I'm trying to create a ODBC connection to SQL server 2000 using SQL server a
uthentication. I have login id "lucky9797" and password "tool" but I'm keep
getting the following error message:
Connection Failed:
SQLState:'28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login Failed for
user "lucky9797"
Could someone please tell me why am I getting this error message? Thanks.
Message posted via http://www.droptable.com1) It might be that the collation sequence indicated at installation time is
case insensitive. Try typing "TOOL" instead of "tool"
"syed rahman via droptable.com" <forum@.droptable.com> wrote in message
news:0e43caa46e9040489d8c88e56a57a606@.SQ
droptable.com...
> Hi,
> I'm trying to create a ODBC connection to SQL server 2000 using SQL server
authentication. I have login id "lucky9797" and password "tool" but I'm keep
getting the following error message:
> Connection Failed:
> SQLState:'28000'
> SQL Server Error: 18456
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login Failed for

user "lucky9797"
> Could someone please tell me why am I getting this error message? Thanks.
> --
> Message posted via http://www.droptable.comsql

ODBC connection failed, but...

Running a database using sql server. User, rights, password set up on the server side and is functioning fine on the users normal desktop. But here is the problem. When the user goes to a different desktop they have never been on before and a new user profile is created on that desktop, they are unable to access the database. When using the front end (access), an odbc connection failure popup occurs when trying to retrieve data. The wierd part is that the odbc connection was set up under admin tools just like it is setup on the working desktop. Also the connection tests successfully. This leads me to believe that it is a desktop side problem and not server side. It is only a problem when a new user profile is setup on a desktop that the user has never been on before.

Any help would be appreciated.

Barry

What is the error the user is getting from the failing desktop - does it say "SQL Server does not exist or access denied"?

Is the SQL Server installed as a default instance listening on the default TCP port 1433 (as opposed to a named instance or a non-default port)?

If the answers are "yes" and "yes", try running

telnet <serverName> 1433

If it reports an error there is an underlying network connectivity problem between the desktop and the server.

If it gives a blank screen the netowkr connectivity works, and further information about your environment and the error may be needed.

You can also follow the checklist posted at

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

|||

Under odbc datasource manager sql setup the libraries is named pipes and the pipes are Serv01and server is serv01, and alias is serv01 as they should be. TCP/IP is Server Serv01 and port is dynamically determined. The exact same setup works under my windows login for myself to access the databse, but will not work when logging in my secretary on my laptop. By the way her name and password are setup correct in the odbc datasource manager. She is able to access the database from her normal workstation though.

The only error we get is odbc connection failure when accessing from the front end at my station. By the way, if I login to her station and go through the sql setup under the odbc manager, I have the same problem at her station that she has at mine.

I'll follow the checklist you posted to see if it helps, but it has to be a local software problem. We'll see though.

Thanks

|||

Nothing like solving your own problem. Well it appears that the naming under the odbc setup manager is highly specific and is not just a name. The problem was that when it asked for a name during setup, "Training Manager" was entered instead of "TrainingManager" without the space. I did not realize that there was connectivity value associated with the name at that point in the setup. The "test connectivity" at the end of the setup will say "successful" with the name written either way, but actually connection to and retrieval of info from the database from front end software will fail.

Hope this helps someone else down the road.

|||

I got the same problem, I am using 2003 server with SQL server 2000. With the same user, I can logon into server but can't create odbc connection beside "SA" login with blank password. This problem happened on our desktop too. Moveover, this problem extend to the application server "Great Plain - Dynamics 9.0". If the user was created on the server side, user can not login properly, unless user's password to be re-entered on the desktop(Windows XP).

I am woundering is the problem caused by encoding issue (Unicode or ASCII). Is there anyone can help?

sql