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
netsh advfirewall firewall add rule name=SQLPort dir=in protocol=tcp action=allow localport=1433 remoteip=localsubnet profile=DOMAIN
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
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.
- 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.
- Expand SecurityLogins
- Right click and select New Login…
- 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.
- Select Server Roles and select serveradmin and sysadmin, leave any existing boxes ticked.
- Hit OK
- 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;
EXEC master..sp_addsrvrolemember @loginame = N’BUILTIN\Administrators’, @rolename = N’sysadmin’
EXEC master..sp_addsrvrolemember @loginame = N’BUILTIN\Administrators’, @rolename = N’serveradmin’
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.
- 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.