Archive

Posts Tagged ‘xp_instance_regread’

SQL Server – Finding TCP Port Number SQL Instance is Listening on

01.05.2013 4 comments

By default SQL Server listens on TCP port number 1433, and for named instances TCP port is dynamically configured. There are several options available to get the listening port for SQL Server Instance.

Here are a few methods which we can use to get this information.

Method 1: SQL Server Configuration Manager

Method 2: Windows Event Viewer

Method 3: SQL Server Error Logs

Method 4: sys.dm_exec_connections DMV

Method 5: Reading registry using xp_instance_regread

 


Let's see how you can use each of these methods in detail:

Method 1: SQL Server Configuration Manager:

Step 1. Click Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager

Step 2. Go to SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for <Instance Name>

Step 3. Right Click on TCP/IP and select Properties

image

Step 4. In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAll group.

image

If SQL Server if configured to run on a static port it will be available in TCP Port textbox, and if it is configured on dynamic port then current port will be available in TCP Dynamic Ports textbox. Here my instance is listening on port number 61499.

 


Method 2: Windows Event Viewer:

When SQL Server is started it logs an event message as 'Server is listening on [ 'any' <ipv4> <port number>' in windows event logs. Here <port number> will be actual port number on which SQL Server is listening.

To view this using Event Viewer:

Step 1. Click Start > Administrative Tools > Event Viewer.

Note: If Administrative Tools are not available on Start menu, go to Start > Control Panel > System and Maintenance > Administrative Tools > View event logs

Step 2. Navigate to Event Viewer > Windows Logs > Application

Step 3. Since huge amount of event are logged, you need to use filtering to locate the required logs. Right click on Application and select Filter Current Log…

image

Step 4. You can filter the events by Event ID and Event source. The event we are interested in has Event ID of 26022, and it’s source is SQL Server Instance. You need to filter by both Event ID and SQL Server Instance if you have multiple instances installed, for a single instance you can filter by Event ID only. Click on OK to apply the filter.

image

Step 5. Once the filter is applied, Locate message 'Server is listening on [ 'any' <ipv4> …'. As we can see from below screenshot that SQL Server Instance is running on TCP Port 61499.

image

 


Method 3: SQL Server Error Logs:

When SQL Server is started it also logs an message to SQL Server Error Logs. You can search for port number in SQL Server Error Logs by opening SQL Server Error Log in notepad or via T-SQL using extended stored procedure xp_ReadErrorLog as below:

EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'

GO

Result Set:

LogDate                  ProcessInfo Text

2013-03-21 13:34:40.610  spid18s     Server is listening on [ ‘any’ <ipv4> 61499].

2013-03-21 13:34:40.610  spid18s     Server is listening on [ ‘any’ <ipv6> 61499].

 

(2 row(s) affected)

As we can see from the output that SQL Server Instance is listening on 61499.

Note: This method does not work if SQL Server Error Logs have been cycled. See sp_Cycle_ErrorLog for more information.

 


Method 4: sys.dm_exec_connections DMV:

DMVs return server state that can be used to monitor SQL Server Instance. We can use sys.dm_exec_connections DMV to identify the port number SQL Server Instance is listening on using below T-SQL code:

SELECT local_tcp_port

FROM   sys.dm_exec_connections

WHERE  session_id = @@SPID

GO

Result Set:

local_tcp_port

61499

 

(1 row(s) affected)

As we can see from the output… same as above Smile

 


Method 5: Reading registry using xp_instance_regread:

Port number can also be retrieved from Windows Registry database.

We can use extended stored procedure xp_instance_regread to get port number information using below T-SQL code:

DECLARE       @portNumber   NVARCHAR(10)

 

EXEC   xp_instance_regread

@rootkey    = 'HKEY_LOCAL_MACHINE',

@key        =

'SoftwareMicrosoftMicrosoft SQL ServerMSSQLServerSuperSocketNetLibTcpIpAll',

@value_name = 'TcpDynamicPorts',

@value      = @portNumber OUTPUT

 

SELECT [Port Number] = @portNumber

GO

Result Set:

Port Number

61499

 

(1 row(s) affected)

As we can see … same as above Smile Smile

Note: The above code will only work if SQL Server is configured to use dynamic port number. If SQL Server is configured on a static port, we need to use @value_name = 'TcpPort' as opposed to @value_name = 'TcpDynamicPorts'.

Hope This Helps!

Vishal

Categories: Management Views and Functions, SQL Configuration, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Undocumented Functions Tags: 2008 R2, application event logs, check sql port, check sql server port no, configuration manager, Database, dmv, dm_exec_connextions, error logs, errorlos, event id, event id 26022, event viewer, find sql server port, finding the port number for a particular sql server instance, how to check the port number in sql server, how to determine whar sql server tcp ip port is being used, how to find port for sql, how to find sql runnig port, how to find sql server runnig port, how to find the port for ms sql server 2005, how to find the port for ms sql server 2008, how to find the port for ms sql server 2008 r2, how to find the port for ms sql server 2012, how to know sql server port number, identify sql server port, identify sql server port being used, identify tcp ip port, identify tcp ip port sql server, instance specific registry, ipall, key, local tcp port @@spid, local_tcp_port, logdate, Management Studio, processinfo, protocols for sql sevrer, Query, read registry tsql, rootkey, scripts, server is listening on, session_id, sp cycle error log, spid, sp_cycle_errorlog, SQL, Sql & Me, SQL 2005, sql 2005 port, SQL 2008, sql 2008 port, SQL 2008 R2, sql 2008 r2 port, sql 2012, sql 2012 port, Sql And Me, sql events, sql registry read reading registry using tsql reading registry using sql server, SQL Server, SQL Server 2005, sql server 2005 how to check port, SQL Server 2008, sql server 2008 how to check port, SQL Server 2008 R2, sql server 2008 r2 how to check port, sql server 2012, sql server 2012 how to check port, sql server change port, sql server configuration manager, sql server default port, sql server determine port, sql server error logs, sql server instance port, sql server listening port, sql server named instance port, sql server where can i find the tcp port configured, Sql&Me, SqlAndMe, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, SSMS, supersocketnetlib, sys.dm_exec_connections, tcp, tcp dynamic ports, tcp port, tcp/ip port number, tcpip port, TSQL, value, value_name, Vishal Gajjar, what tcp port is sql server running under, windows logs application, xp instance regread, xp read error log, xp regread, xp_instance_regread, xp_readerrorlog, xp_regread

SQL Server – Identifying default data directory for multiple instances through registry

05.01.2012 9 comments

This is a follow up post from a comment on my blog.

Earlier I posted about using xp_instance_regread to get default data location for current instance. It translates a given registry path to instance specific registry path. This can return the value for a single instance only. However, if you need to locate default data directories for all available instances on a machine, you need to use xp_regread, which reads from an absolute registry path.

Registry path for default data location is same for different SQL Server versions:

HKLMSOFTWAREMicrosoftMicrosoft SQL Server<InstanceName>MSSQLServer

 

So the first thing we need before we can start reading data location from registry is list of instance name; these also can be read from registry using xp_instance_regenumvalues.

Registry path for instance name is as follows:

HKLMSOFTWAREMicrosoftMicrosoft SQL ServerInstance NamesSQL

We need to retrieve these to a temporary structure so that we can use it further:

CREATE TABLE #tempInstanceNames

(

      InstanceName      NVARCHAR(100),

      RegPath           NVARCHAR(100),

      DefaultDataPath   NVARCHAR(MAX)

)

 

INSERT INTO #tempInstanceNames (InstanceName, RegPath)

EXEC   master..xp_instance_regenumvalues

       @rootkey = N'HKEY_LOCAL_MACHINE',

       @key     = N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

      

SELECT      InstanceName, RegPath, DefaultDataPath

FROM        #tempInstanceNames

Result Set:

InstanceName  RegPath                    DefaultDataPath

MSSQLSERVER   MSSQL10_50.MSSQLSERVER     NULL

DENALI3       MSSQL11.DENALI3            NULL

SQL08ENT      MSSQL10.SQL08ENT           NULL

SQL05EXP      MSSQL.1                    NULL

We have a list of instance names; now we need to iteratively read registry value for each path; and append it to DefaultDataPath. The paths we need to read from registry will be:

1. HKLMSOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLServer

2. HKLMSOFTWAREMicrosoftMicrosoft SQL ServerMSSQL11.DENALI3MSSQLServer

3. HKLMSOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10.SQL08ENTMSSQLServer

4. HKLMSOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServer

These needs to be read using xp_regread. I have used dynamic SQL to iterate through the list:

DECLARE     @SQL VARCHAR(MAX)

SET         @SQL = 'DECLARE @returnValue NVARCHAR(100)'

SELECT @SQL = @SQL + CHAR(13) +

'EXEC   master.dbo.xp_regread

@rootkey      = N''HKEY_LOCAL_MACHINE'',

@key          = N''SOFTWAREMicrosoftMicrosoft SQL Server' + RegPath + 'MSSQLServer'',

@value_name   = N''DefaultData'',

@value        = @returnValue OUTPUT;

 

UPDATE #tempInstanceNames SET DefaultDataPath = @returnValue

WHERE RegPath = ''' + RegPath + '''' + CHAR(13) FROM #tempInstanceNames

 

EXEC (@SQL)

 

SELECT      InstanceName, RegPath, DefaultDataPath

FROM        #tempInstanceNames

Result Set:

InstanceName         RegPath                    DefaultDataPath

MSSQLSERVER          MSSQL10_50.MSSQLSERVER     C:DatabaseData

DENALI3              MSSQL11.DENALI3            C:DatabaseDenaliData

SQL08ENT             MSSQL10.SQL08ENT           C:Database2008Data

SQL05EXP             MSSQL.1                    C:Database2005Data

I have updated the column in #tempInstanceNames so that it can be used further.

You can download the script from here.

Hope This Helps!

Vishal

SQL Server – Hide an Instance of SQL Server

23.08.2011 1 comment

You can browse available SQL Instances on network by choosing "<Browse for More..>" from Server Name drop-down list in "Connect to Server" dialog box in Management Studio:

image

As you can see from the screen shot, all of my instances are visible on the network. If I want to hide this information from people on my network, it can be done easily. SQL Server allows us to hide an instance from being listed on the network. This can be done using SQL Server Configuration Manager.

For example, If I need to hide my Developer Edition instance SQL01:

1. Launch SQL Server Configuration Manager

2. Under "SQL Server Network Configuration" > Right-Click "Protocols for SQL01" > Select Properties

3. Set "Hide Instance" value to "Yes" from the drop-down list:

image

4. The Instance needs to be restarted in order for changes to take effect:

image

Once the instance is restarted it will not longer appear in the "Network Servers" list:

image

 

Hiding an Instance using T-SQL:

The information to hide/show instance is stored in registry. You can use extended stored procedure xp_instance_regwrite to update the registry value to hide/show instance. Below T-SQL will hide the instance. To unhide instance set @value to 0 :

EXEC master..xp_instance_regwrite

      @rootkey = N'HKEY_LOCAL_MACHINE',

      @key =
N'SOFTWAREMicrosoftMicrosoft SQL Server
MSSQLServerSuperSocketNetLib',

      @value_name = N'HideInstance',

      @type = N'REG_DWORD',

      @value = 1

      — 0 = No, 1 = Yes

To check if an instance is hidden you can use xp_instance_regread to check registry values:

DECLARE @getValue INT

EXEC master..xp_instance_regread

      @rootkey = N'HKEY_LOCAL_MACHINE',

      @key=

N'SOFTWAREMicrosoftMicrosoft SQL ServerMSSQLServerSuperSocketNetLib',

      @value_name = N'HideInstance',

      @value = @getValue OUTPUT

SELECT @getValue

 

This method only prevents the instance from being listed on the network, It does not prevent users from connecting to server if they know the instance name.

 

Hope This Helps!

Vishal

SQL Server – Server-related dynamic management views in SQL Server 2008 SP1 and “Denali”

29.07.2011 1 comment

There are three new server-related DMVs introduced in SQL Server 2008 R2 Service Pack 1 and SQL Server Codename "Denali".

 

1. sys.dm_server_services:

– For SQL Server 2008 R2 SP1, it contains information about SQL Server and SQL Server Agent services for the current instance.

– For "Denali", it returns information about SQL Server, Full-Text Search and SQL Server Agent services for the current instance. Let’s use below query on both:

SELECT servicename          AS [Name],

       startup_type_desc    AS [Type],

       status_desc          AS [Status],

       process_id           AS [ProcessID],

       service_account      AS [Startup Account]

FROM   sys.dm_server_services

Result Set for SQL Server 2008 R2 SP1:

Name                           Type       Status         ProcessID   Startup Account

————————–     ——-    ——         ———   —————

SQL Server (MSSQLSERVER)       Automatic  Running        2492        .sqladmin

SQL Server Agent (MSSQLSERVER) Manual     Stopped        NULL        .sqladmin

 

(2 row(s) affected)

Result Set for "Denali":

Name                       Type    Status   ProcessID  Startup Account

————————– ——  ——   ———  —————

SQL Server (DENALI)        Manual  Running  5176       .sqladmin

SQL Server Agent (DENALI)  Manual  Stopped  NULL       .sqladmin

SQL Server Agent (DENALI)  Manual  Stopped  NULL       .sqladmin

 

(3 row(s) affected)

Ah!, for "Denali" it’s returning two entries for SQL Server Agent, and not returning the Full-Text Search service, (it is installed), a possible "Denali" bug (Anyone else got the same issue?).

 

2. sys.dm_server_registry:

– For both SQL Server 2008 R2 SP1 and "Denali" – this view returns configuration and installation information, such as host machine or network configuration for the current instance, i.e. information we used to fetch using xp_instance_regred for an instance, Let’s execute below query on both:

SELECT registry_key, value_name, value_data

FROM   sys.dm_server_registry

WHERE  value_name = N'CurrentVersion'

Result Set for SQL Server 2008 R2 SP1:

registry_key                                    value_name    value_data

——————————-                 ———–   ————

HKLMSoftware…MSSQLServerCurrentVersion   CurrentVersion       10.50.2500.0

 

(1 row(s) affected)

Result Set for "Denali":

registry_key                                    value_name    value_data

——————————-                 ———–   ————

HKLMSoftware…MSSQLServerCurrentVersion    CurrentVersion       11.0.1440.19

 

(1 row(s) affected)

 

3. sys.dm_server_memory_dumps:

This view returns a list of memory dump files generated by the SQL Server Database Engine. It returns below information:

i) filename – File name and physical path of the memory dump file,

ii) creation_time – date and time the memory dump file creation,

iii) size_in_bytes – memory dump file size in bytes.

 

Hope This Helps!

Vishal