TCP Port of SQL Server

By default the default TCP Port for SQL Server is 1433.  It can be changed for security purpose to minimize the potential threat of a hacker trying to access through the default port number.  But that is whole together a different story.

Every now and then somebody posts this question on forums- how can I tell what port my SQL Server instance is running on?  As sometimes while accessing SQL Server remotely you may need to add the TCP Port in the connection string.

There are more than 1 way to find out the answer.  From SQL Server Error Logs, Configuration Manager, Command Prompt, Registry Editor.   But today I will share a simple T-SQL script with you, from which you can easily find out the TCP port SQL Server instance is running on.

First lets see in regedit where to look for the Port number. 
The location of port number for SQL Server 2005 -

For default instance : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP

For Named Instance :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ [Instance Name]\MSSQLServer\SuperSocketNetLib\Tcp

If you don’t know how to open the regedit :
1. Click on Start and then on Run.
2. In run type regedit and click on Ok.

After checking out the port number manually now lets see the query I created.  It uses a non-documented extended stored procedure xp_regread.  Basically it is helpful in reading from the registry via Transact-SQL.  Since it is a non-documented extended stored procedure you won’t find any reference about xp_regread in Books Online. If you want to know more about it, best thing you can do is Google for xp_regread.  So here is the script which will give you the TCP port SQL Server is running on :

DECLARE @TcpPort VARCHAR(5)         ,@RegKey VARCHAR(100) IF @@SERVICENAME !='MSSQLSERVER'     BEGIN         SET @RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @@SERVICENAME + '\MSSQLServer\SuperSocketNetLib\Tcp'     END     ELSE     BEGIN         SET @RegKey = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP'     END EXEC master..xp_regread     @rootkey = 'HKEY_LOCAL_MACHINE'     ,@key = @RegKey     ,@value_name = 'TcpPort'     ,@value = @TcpPort OUTPUT SELECT @TcpPort AS PortNumber         ,@@SERVERNAME AS ServerName         ,@@SERVICENAME AS ServiceName

You will get the output something like this :

Other simple way of finding out the TCP port is from the Configuration Manager.
1. Start >> All Program >> Microsoft SQL Server 2005 >> Configuration Tools >> SQL Server Configuration Manager .
2. In the configuration manager click on SQL Server 2005 Network Configuration.
3. Then click on the Instance name for which you want to find out the Port Number.
4. Double click on TCP/IP.
5. Finally on IP Addresses, and you will see something like :

Expiration Date of SQL Server Evaluation Edition

If you have installed the Evaluation / Trial Version of SQL Server, you may like to find out what is the expiry date of the Trial Version.  The Trial Edition or Evaluation Edition of SQL Server will expire exactly 180 days after the install date.  Using it after the 180 days period is violation of MICROSOFT evaluation license terms.  Anyways after 180 days when an install of Evaluation Edition Expires, the SQL Server services stop functioning.  So you must purchase the license for SQL Server and upgrade the Evaluation copy before the expiry date of the Evaluation.

But then how to find out the exact expiry date of Trial Version?  There are 2 ways to find out

• From installation logs
• From Management Studio

From installation logs
A summary.txt file is get created during the installation of SQL Server.  So this summary.txt can give you the exact date and time of the SQL Server installation.  And the trial software will automatically expire after six months.  You can find summary.txt file under

<Drive>\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt

At the end of the file you can see something like

Setup succeeded with the installation, inspect the log file completely for status on all the components. Time : Tue Dec 30 15:59:55 2008

So after 180 days counting from 30 Dec 2008 i.e. 28 Jun 2009 the SQL Server Evaluation Edition will be expired.

From Management Studio
Now this is very simple. Open the Management Studio, click on the “Help” of Menu Bar and then on “About”.  A new window will appear, where you will see some thing like:

Microsoft SQL Server Management Studio (expires in 102 days)

See the Image :

SQL Server Versions and Editions.

Lets see how to find which Sql Server Version is installed and Service Packs applied on your machine-

To know Execute

SELECT @@version

If you are on Sql Server 2000 or later use following which will give you a more elegant output -

SELECT SERVERPROPERTY('productversion') as VersioNumber
,SERVERPROPERTY ('productlevel') as SPLevel
,SERVERPROPERTY ('edition') as ServerEdition

And here is detailed chart which will show you SQL Server version numbers and the corresponding product or service pack level -

Sr No	Version Number	Release	SP Level
10.1	10.0.1600.22	SQL Server 2008	RTM
-	-	-	-
9.1	9.0.1399.00	SQL Server 2005	RTM
9.2	9.0.2047.00	SQL Server 2005	SP1
9.3	9.0.3042.00	SQL Server 2005	SP2
9.4	9.0.4035.00	SQL Server 2005	SP3
-	-	-	-
8.1	8.00.194.00	SQL Server 2000	RTM
8.2	8.00.384.00	SQL Server 2000	SP1
8.3	8.00.534.00	SQL Server 2000	SP2
8.4	8.00.760.00	SQL Server 2000	SP3
8.5	8.00.2039.00	SQL Server 2000	SP4
-	-	-	-
7.1	7.00.623.00	SQL Server 7.0	RTM
7.2	7.00.699.00	SQL Server 7.0	SP1
7.3	7.00.842.00	SQL Server 7.0	SP2
7.4	7.00.961.00	SQL Server 7.0	SP3
7.5	7.00.1063.00	SQL Server 7.0	SP4
-	-	-	-
6.51	6.50.201.00	SQL Server 6.5	RTM
6.52	6.50.213.00	SQL Server 6.5	SP1
6.53	6.50.240.00	SQL Server 6.5	SP2
6.54	6.50.258.00	SQL Server 6.5	SP3
6.55	6.50.281.00	SQL Server 6.5	SP4
6.56	6.50.415.00	SQL Server 6.5	SP5
6.57	6.50.416.00	SQL Server 6.5	SP5a
6.58	6.50.479.00	SQL Server 6.5	SP5a update
-	-	-	-
6.01	6.00.121.00	SQL Server 6.0	RTM
6.02	6.00.124.00	SQL Server 6.0	SP1
6.03	6.00.139.00	SQL Server 6.0	SP2
6.04	6.00.151.00	SQL Server 6.0	SP3

- Mangal Pardeshi

How To Enable Xp_CmdShell in Sql Server?

Today I will show you how to enable the Xp_CmdShell extended stored procedure in Sql Server 2005 and 2008.
Well normal error message you'll get when Xp_CmdShell is not enabled on your Sql Server and you to try execute some Windows commands using Xp_CmdShell is

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.


You can enable the Xp_CmdShell using 2 ways, either by executing T-sql statements or from
"Surface Area Configuration Manager". We'll see both of them.


A. Enable Xp_Cmdshell from Management Studio.
For enabling Xp_CmdShell from Management Studio you need to execute following code.

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

B. Enable the Xp_CmdShell from "Surface Area Configuration Manager."

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Features.
3. Click on xp_cmdshell and tick on checkbox of "Enable the xp_cmdshell".



- Mangal Pardeshi.

How to configure SQL Server 2005 to allow remote connections

By default, SQL Server 2005 does not allow remote connections. To configure SQL Server 2005 to allow remote connections, we need to follow the steps:

• Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.
• Turn on the SQL Server Browser service. (If you are using Named Instance or Sql Express.)
• Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.
  
  So we will see how to achieve above 3 points one by one.

A] How to Enable remote connections for SQL 2005?
1. Click on Start >> Programs >> Microsoft SQL Server 2005 >> Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.
4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

B] How to Enable the SQL Server Browser service?

If you are running SQL Server 2005 by using an instance name and you are not using a specific TCP/IP port number in your connection string, you must enable the SQL Server Browser service to allow for remote connections.

1. Click on Start >>Programs >> Microsoft SQL Server 2005 >> Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, click SQL Server Browser, click Automatic for Startup type, and then click Apply.

Note: When you click the Automatic option, the SQL Server Browser service starts automatically every time that you start Microsoft Windows.
4. Click Start, and then click OK.

C] How to Create exceptions in Windows Firewall?

If you are running a firewall on the computer that is running SQL Server 2005, external connections to SQL Server 2005 will be blocked unless SQL Server 2005 and the SQL Server Browser service can communicate through the firewall. You must create an exception for each instance of SQL Server 2005 that you want to accept remote connections and an exception for the SQL Server Browser service.

To open Windows Firewall, click Start, click Run, type firewall.cpl, and then click OK.

1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe executable program, click Open, and then click OK. Note The path may be different depending on where SQL Server 2005 is installed. MSSQL.1 is a placeholder for the instance ID that you obtained in step 3 of the previous procedure.
4. Repeat steps 1 through 3 for each instance of SQL Server 2005 that needs an exception.

To create an exception for the SQL Server Browser service in Windows Firewall, follow these steps:
1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program, click Open, and then click OK.

Note: The path may be different depending on where SQL Server 2005 is installed.

The source - http://support.microsoft.com/kb/914277

- Mangal Pardeshi.

Some Useful Views And SPs in Sql Server.

Here is the good collection of views and Store Procedure which can be very useful.
Applicable for Sql Server 2005 and +

VIEWS : To use all the views you just need to do SELECT * FROM VIEW_NAME
e.g SELECT * FROM sys.databases

• sys.databases : Lists all the databases in Sql Server.
• sys.tables : Lists all the Tables in the database.
• sys.views : Lists all the views in the database.
• sys.procedures : Lists all the Procedures in the database.
• sys.triggers : Lists all the Triggers in the database.

• sys.columns : Lists all the columns of tables in database.
• sys.syscolumns : Lists all the columns in database including of those SP.
• sys.key_constraints : Lists all primary key or unique constraints in database. For primary key TYPE = 'PK' and for unique keys TYPE = 'UQ'
• sys.check_constraints : Lists all the Check Constraints in the database.
• sys.default_constraints : Lists all the Default Constarints in the database.
• sys.foreign_keys : Lists all the Foreign Keys in the database.

• sys.syslogins : Lists all the login names in server.
• sys.sql_logins : Lists all the Sql Authentication Logins in server.
• sys.sysusers : Lists all the users in database.

-- I'll Add some more very soon.

- Mangal Pardeshi.

Sql Server Connection Issues - Msg 18456

Now I'll try to help you understand the connection issues with Sql Server.
Today we will focus on the error

"Msg 18456, Level 14, State 1, Server , Line 1"
"Login failed for user ''."

This error is nothing but an authentication failure that involves a bad password or user name. So whenever you get a similar error be sure that is somthing related to user name or password.
And key to solve the issue is STATE number you get in the error.

• 2 and 5 : Invalid userid.
• 6 : Attempt to use a Windows login name with SQL Authentication.
• 7 : Login disabled and password mismatch.
• 8 : Password mismatch.
• 9 : Invalid password.
• 11 and 12 : Valid login but server access failure.
• 13 : SQL Server service paused.
• 16 : User don't have permission to log into Server.
• 18 : Change password required.

And as it seems you get the error Msg 18456 when you trying to connect Sql server in Sql Authentication.

Some points I like to add....

State 23 : Is one of the very rare errors, normaly happens when you try to connect to server when it is shutting down.

States 11 and 12 : When the Windows user trying to access the server and he doesn't have rights. Chances are you are on Windows Vista :).

- Mangal Pardeshi.