A few of our clients have only 1 or 2 PCs (no server) and as our apps all use SQL Server as a backend data store I have often attempted to configure the firewall to allow SQLServer traffic. And failed. I decided to find out the correct way to do it the other day.

1. Add an exception for C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlserver.exe. Assuming the first instance and installed on the C Drive.

2. Add an exception for C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe.

3. Ensure that the sql browser service is started and set to start automatically.

4. Via Surface Area Configuration for Services and Connections, allow local and remote connections.

That's it! Pretty easy and logical in the end. 

I came across a very annoying problem recently where I was using management studio to connect to a hosted SQL Server (2005) and I wasn't connecting as a  sysadmin as I didn't have access to all DBs on the server.

Under SQL Management Studio 2005 I could connect to the server and expand the databases node to see all databases. But under Studio 2008 when I tried to expand the databases node I got the following error:

Installed Management Studio 2008 the other day and have been struggling with it ever since. It's been winning until now. I try to add new columns in their place within the table structure. However I couldn't save any changes because I was not allowed to save changes if I added a new column in the middle of the column list. If I added one at the end it would save ok. When I ran into this problem I would flick back to Management Studio 2005 to do the change. 

Pulled my finger out and decided to see if there was a setting that controlled this behaviour. Found this article by Brian Knight on the issue:

SQL Server 2008 Designer Behaviour Change