Setting up MS SQL 2007 Express for access via TCP
pmccurdy has been doing some work with us lately, and got to have some "experiences" with Microsoft SQL 2005 Express Edition. Here's a variation on his guide for how you set it up for the first time so that you can access it from a Linux machine over TCP. Blame me for any errors, since I edited it.
- Install as normal. Believe that life may yet be worth living.
- Ignore the Surface Area configuration thingy. As far as I can tell, its only trick is occasionally crashing. It sure does a bang-up job of reducing your surface area for successful connections from Linux though.
- Run the SQL Server Configuration Manager.
- Select "SQL Server 2005 Network Configuration", then "Protocols for SQLEXPRESS" (or your selected instance name).
- Enable TCP/IP.
- Right click TCP/IP, pick Properties.
- Click the "IP Addresses" tab.
- Under the "IPAll" pseudo-section, delete the entry for "TCP Dynamic Ports" and set "TCP Port" to 1433 (deleting the dynamic port entry may not be needed, but makes it more obvious what's going on if you try to connect with Microsoft's client).
- Click OK, acknowledge the dialog box.
- Go to SQL Server 2005 Services, and restart the SQL Server service.
- Drown your sorrows.
ssh+2FA to all your machines, anywhere, without opening firewall ports.