The day without SQL Browser

When you are in a highly secured environment, SQL Server Browser maybe not be allowed, then what will happen? First of all, we need take a look of SQL Browser’s role in SQL Server.

SQL Browser is a replacement for (SQL Server Resolution Protocol) SSRP in SQL Server 2005. SSRP was first introduced in SQL Server 2000 with multiple instances supportability and listens on UDP 1434. SQL Browser runs on TCP 1434 by default, and functions for redirect client connection to correct port, include SQL Server instances run on nondefault port, SQL Server instances dedicated administrator connection and SQL Server Analysis Service instances.

So if you want disable SQL Browser, you’d better think about this first. How client can connect to a specific service instance? In most cases, default database engine instance was running on TCP 1433. But this can be changed due to your networking hardenning policy. And named instances, DAC, SQL Server Analysis Service instances are all running on dynamci ports. Now SQL Browser will not in function, so you cannot depend on dynamic port any more. You have to specify static port for SQL Server Database Engine instances, DAC for SQL Server Database Engine instances and SQL Server Analysis Service instances.

After you assigned static port for all these instances, then you will also need to know how to connect to those services with specified port. So let’s take a look how to make this happen.

Database Engine instances

This is quite straightforward. You can easily get it done in SQL Server Configuration Manager.

And connection to database engine instances is also very straightforward. You just change the server name part in your connection to “SERVER_NAME,PORT_NUMBER”. For example, you want connect to the instance running on 11433 port of machine DBSRV. You will put “DBSRV,11433” as server name.

DAC

To assign a static port for SQL Server Dedicated Administrator Connection, you will need to edit your registry. 😦

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

You will find a string key “TcpDynamicPorts” there. And by default the value is 1434. You need update this value to the port number you preferred.

And, don’t forget to enable remote administration connection if you plan connect DAC remotely. You need execute following command:

EXEC sp_configure ‘remote admin connections’, 1
GO
RECONFIGURE
GO

To connect DAC with fixed port, just like connect database engine with non-default instance, use server name like SERVER_NAME,PORT_NUMBER. For example, you want connect to the instance which already changed its DAC port to 11434. You will put “DBSRV,11434” as server name. So with sqlcmd, you just type sqlcmd -S DBSRV,11434.

SQL Server Analysis Service instances

To assign a static port for OLAP service, you will need to do some text edit.

First you need find a file called “msmdsrv.ini”. You can find this file under your OLAP installation directory. If you install your Analysis Service to it’s default location, then it will be “C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Config”. You need pay attention to the part in bold font. Because you may chose to install multiple Analysis Service instances on a same machine. So you need take care which instance you are editing. The part before dot is your Analysis Service version, 9 for 2005, 10 for 2008. The part after dot is instance name, MSSQLSERVER means default instance. If the Analysis Service was clustered, then the file will be at the shared disk.

After you found the file, you need open it and found the line “<Port>0</Port>”. You need change the port number value to the one assigned for OLAP service. After you saved the file, you need restart Analysis Service to make it effect.

To connect an Analysis Service with static port, you need use server name like SERVER_NAME:PORT_NUMBER.

Now you don’t need SQL Browser any more.

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: