Enabling SQL Sever on 800 computers at once

Now that I'm teaching a class in databases, it's useful to actually have a database that my students can use when they need to do their projects. Unfortunately, Sql Express (and other versions of SQL server) do not have TCP/IP turned on by default and even if they did, the default is to use dynamic ports, which of course is no good. The reasons for this security through obscurity was the sql slammer worm that hit several years ago, so for "security" (read: so Mort can be stupid without dire consquences) it disables all connectivity with the outside world. It's fairly simple to change the settings, however I can't do it manually on 800 computers. In addition to this, students (no admin access) need to be able to access the instance of Sql Server to create new databases, so that has to be done as well. In this post, I'll show exactly how I did this, just in case anyone else ever needs to do something similar on their own network.

The humble .bat file

For this exercise, I chose a .bat file as my delivery vehicle. They're pretty easy to write and they work on every computer regardless of what you have installed, which is convenient when other more powerful tools are available. Also, I don't need anything more powerful to run a few commands.

The requirements are pretty simple: IT is pretty slammed right now so it needs to be a script that can be run completely unattended, it can't affect anyone working on the remote computer, it shouldn't break anything if it's run multiple times, and if it fails, the fix should be to run it again.

First, enable Sql server

First step is to enable Sql Server to listen on a specific port. There's no command line option to just enable TCP/IP, and obviously using the Sql Server Configuration Tool is out of the question. However, the Surface Area Configuration Tool is an option, and it turns out that tool can clone settings from another instance of Sql Server. All I have to do is enable TCP/IP on my instance, and then run SAC.exe to create a file that has my settings in it. The SAC.exe tool is in the c:\Program Files (x86)\Microsoft Sql Server\90\Shared directory. The command to generate the settings file is "sac.exe out " and there are other options, but the default (export everything) is fine in this case.

To run this on any arbitrary computer, however, the "C:\Program Files (x86)" is no good, since that directory could be anywhere, however Windows conveniently gives us an environment variable called %programfiles% which will be set to whatever directory programs are installed in by default. Finally, the config file could be anywhere (since IT is running this) so let's ensure that we pass that in as a parameter to the .bat file. Therefore, our first line in the file is this:

%programfiles%\Microsoft Sql Server\90\Shared

Setting the default port

The default port Sql is supposed to listen on is 1433, and if it's not on this port then you'll have to pass the port as a param to sqlcmd (or whatever) and I don't want to do that. Also, by default, the port is dynamic (set to 0) so it could change every time the service is restarted, which is no good. It turns out that the only way to change the port outside of the configuration tool is by editing the registry. It turns out that in a .bat file, this is easy. The command is "REG ADD" and then a bunch of parameters. The registry key for where the port lives for Sql Server is here:

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll

We need to create a value here called "TcpPort" and set it to 1433. The command to do this is:

REG ADD "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll" /v TcpPort /t REG_SZ /d 1433 /f

The /f is necessary to ensure that it doesn't prompt if the key already exists (it might; remember, this has to be able to run multiple times without error). However, the Sql service needs to be stopped and restarted in order for this to take effect. I can use "net start" and "net stop" to accomplish this. Therefore, the next three lines in our .bat file are:

net stop "SQL Server (SQLEXPRESS)"
REG ADD "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll" /v TcpPort /t REG_SZ /d 1433 /f
net start "SQL Server (SQLEXPRESS)"

Giving students permissions

Now that SQL is configured in a useful way, I need to grant some roles to the students. These will be in a .sql script, so all I have to do is run it (we'll examine the script in a minute). The way to run scripts is with sqlcmd, which should be part of the system %PATH% so I don't need to type the full path name, I can just use "sqlcmd" and now that the server is on the default port, it should work. I'll be passing in the location of a .sql file anyway, so let's make that a command line param as well. The final line of our .bat file is therefore this:

sqlcmd -E -S localhost -i %2

This tells sqlcmd to use a trusted connection and connect to the instance on localhost and read and execute the contents of the supplied file. Therefore, the full .bat file looks like this:

%programfiles%\Microsoft Sql Server\90\Shared

net stop "SQL Server (SQLEXPRESS)"
REG ADD "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll" /v TcpPort /t REG_SZ /d 1433 /f
net start "SQL Server (SQLEXPRESS)"

sqlcmd -E -S localhost -i %2

So what's in the sql script?

In the .sql file, I need to enable some roles. Since all students are domain users, I felt it would be simplest to just add that group to the appropriate roles. You probably don't want to do this on your network; would be best to create a "DBUSERS" group or maybe a "DEVELOPERS" group. Not horribly important here though. Let's look at the script.

First, we need to grant DB access to a group. That's pretty easy, just use sp_grantdbaccess (that's a stored procedure) that takes two arguements, the group and then a sql group to create. We'll need that group to assign to the correct role. The command therefore is this:

sp_grantdbaccess "digipen.edu\domain users", "domainusers"

Next, we want the students to have db_owner permissions so that they can do things without being an admin, although I honestly don't care what level of access the students have since it's an academic setting. The way I grant db_owner is to use the group I just created and sp_addrolemember, so the next line is this:

sp_addrolemember "db_owner", "domainusers"

Finally, they need to be able to create databases. The role for this is "dbcreator" but it's a different kind of role (I don't totally understand this) and therefore I have to add it via sp_addsrvrolemember and the command is this:

sp_addsrvrolemember "digipen.edu\domain users", "dbcreator"

Don't forget to add the "go" so that it executes. The full .sql file is this:

sp_grantdbaccess "digipen.edu\domain users", "domainusers"
go
sp_addrolemember "db_owner", "domainusers"
go
sp_addsrvrolemember "digipen.edu\domain users", "dbcreator"
go

Now, everything is good and I can run this automatically on every computer with this command:

dbsetup.bat settings.txt addroles.sql

I've run this on hundreds of computers and it works great. Obviously you can put whatever settings you want in this thing and grant whatever roles you need for your environment. Hopefully this will help someone with automating tasks; I sure could have used a post like this a week ago.