I am looking for a SQL Server Authentication script

Question

I am looking for a SQL Server Authentication script

discipledoc

SSC Rookie

Points: 35
More actions
December 8, 2009 at 11:48 am

#136751

Hi,
I am looking for a script that I can use in my GPO that will allow my desktops to:
Point to the correct SQL Server
Choose the correct mode (SQL Server Auth over Windows NT Auth.)
Can such a script be written?
Thanks in advance.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 738773 More actions · Answer 1

I'm not familiar enough with that is possible in a GPO. Can you use WMI scripts? Or could you set some type of environmental variable for the user?

What application are they using on the client? That's what would need to be modified for connecting to SQL Server.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

What you could probably use is a Client Configuration Alias that sits client side and allows you to easily change the SQL Server without having to reconfigure a bunch of ini files or client ODBC settings? If that is the case you can easily do that by calling a .reg file to add/update the SQL Client Configuration Alias settings on the machines at login time:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]

"ECOM_DEV"="DBMSSOCN,SQLDEVDB\\DEV02"

The above is a sample .reg file that creates a alias named ECOM_DEV that resolves to the SQLDEVDB server and the DEV02 named instance on that server. If I moved the databases from DEV02 to DEV01, all I have to do is update this registry file, and tell the users to log off and log back on, and when they do it will update their alias and repoint them to the correct location immediately.

For ODBC you can do similar with the DSN's but by affecting other registry keys:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]

"ECOM_DEV"="SQL Native Client"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ECOM_DEV]

"Driver"="C:\\WINDOWS\\system32\\sqlncli.dll"

"Server"="SQLDEVDB\\DEV02"

"LastUser"="jkehayias"

"Trusted_Connection"="Yes"

Now for the Disclaimer:

Don't just take the above and push a regisitry change to your entire environment that you haven't tested or don't understand how it works. I am not responsible for any damage caused by use or misuse of the examples provided here.

Other than that, hope it helps.

discipledoc SSC Rookie Points: 35 More actions · Answer 3

Thanks, guys.

Jonathan, thanks for the script, and I will edit and test it out.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

One thing I didn't mention is that if you are using an Alias, they have to be setup initially and the application has to be configured to use the Alias. There is a one time effort to establish this, but once it is in place you control the alias from the logon scripting.

discipledoc SSC Rookie Points: 35 More actions · Answer 5

The mode that is being used is SQL server authentication, so there will not be an aliased used.