Ok I admit it, I’m a developer and configuring access to SQL Server is not my bag (baby). SQL 2005 had a nice big Surface Area Configuration button than just allowed access from clients, so I ignored what it was doing, pressed it and got on with my work. However, SQL 2008 seems to be have been targeted at me because it no longer has such a backdoor and forced me to find out what I should do. So I thought I’d write it down for others who prefer to search the web rather then read the documentation <oops>,
First off you need to open the SQL Configuration Manager and check your favourite protocol is enabled, for me that would be TCP/IP. Double click that and select the IP Addresses tab. You’ll notice in there a list of possible IP addresses and ports for SQL to listen on. You can, if you maintain your (mine) lazy tendencies simply change the setting from dynamic to static and plug in port 1433. When clients attempt to connect to your server they attempt to use 1433. Now this isn’t supposed to be a good idea (no idea why – you’ll see why later) so stick with dynamic ports. Now this means that SQL will listening on some port but you’ve no idea what as it may change each time the server starts. So you must start the SQL Browser Service, this acts as the go-between and “tells” the client what port to use. So there you are, you should now be off and running. But why not use 1433? I thought the idea was slow those pesky hackers down, but if you have to open the Browser Service then what does that achieve? Something I need to look into, when I have the time, when I have the time, when I have the time…..