Before you ask, I definitely count myself as a novice when it comes to SQL. Nevertheless, here are a few SQL tips I have picked up along the way…

Enable SQL to communicate through the Windows Firewall

SQL Database
netsh advfirewall firewall add rule name=SQLPort dir=in protocol=tcp action=allow localport=1433 remoteip=localsubnet profile=DOMAIN
SQL Browser
netsh advfirewall firewall add rule name=SQLBrowser dir=in protocol=udp action=allow localport=1434 remoteip=localsubnet profile=DOMAIN
SQL Server executable
netsh advfirewall firewall add rule name=”SQLExe(<SQL Instance name>)” dir=in action=allow program= “c:Program FilesMicrosof
t SQL ServerMSSQL10_50.<SQL Instance name>MSSQLBinnsqlservr.exe” remoteip=localsubnet profile=DOMAIN

Reference:

• Configure the Windows Firewall to Allow SQL Server Access

Make the local Administrators group SQL Admin

It is very useful to have the local Administrators group on a SQL server be SQL Admin. Useful, not necessarily secure or a best practice. To be able to perform these steps you need access to an account that already is an SQL Admin of the server/instance you are trying to change.

1. Open SQL Server Management Studio and log on
   To use Windows Integrated authentication you need to be logged onto the SQL server computer with an account that is already a SQL Admin, it using SQL authentication; just enter the username and password.
2. Expand SecurityLogins
3. Right click and select New Login…
4. In the Login name field enter BUILTINAdministrators.
   For some reason SQL requires that all built in security principals be prefixed with the word BUILTIN instead of the computername.
5. Select Server Roles and select serveradmin and sysadmin, leave any existing boxes ticked.
6. Hit OK
7. Exit SQL Server Management Studio, log off the computer, log on again with a user that is a member of the local Administrators group and open SQL Server Management Studio again. This time use Windows Authentication.

Same thing in Transact-SQL:

CREATE LOGIN “BUILTIN\Administrators” FROM WINDOWS;
GO

EXEC master..sp_addsrvrolemember @loginame = N’BUILTIN\Administrators’, @rolename = N’sysadmin’
GO

EXEC master..sp_addsrvrolemember @loginame = N’BUILTIN\Administrators’, @rolename = N’serveradmin’
GO

If you still cannot get access; make sure you are not running with User Account Control (UAC) enabled. UAC strips the Administrators group from your security token.

Reference:

• PRB: Use BUILTINGroup to Grant Access to Predefined Windows NT Groups
• How to troubleshoot error 15401
• CREATE LOGIN (Transact-SQL)

Test connectivity to a SQL Server

This is pretty cool…

Create an empty file anywhere in the file system called test.udl.

PowerShell: New-Item test.udl –type file

Double click to open it. You will see the following dialogue box:

Enter the servername, select security mode and database, and hit Test Connection.

Reference:

• Creating and Configuring Universal Data Link (.udl) Files

Windows Azure

Reference:

• The Top 10 Things to Know When Running SQL Server Workloads on Windows Azure Virtual Machines

Go DBAs!