Server ‘SERVERNAME1’ is not configured for RPC for a Linked Server

Once in while I am asked to troubleshoot a SQL Server database where my only connection is though a linked server.  Because this database server is on protected network, I don’t have port 1433 open to connect the instance with SQL Server Management Studio.

There are a couple of commands I like to run to check the health of the database.

First, already knowing that the database is running, I like to look at the error log with the xp_readerrorlog extended stored procedure.

When I run a simple command to view the error log from my linked server:

exec SERVERNAME1.master.sys.xp_readerrorlog

Note: replace SERVERNAME1 with the name of your linked server

I get the following error:

Msg 7411, Level 16, State 1, Line 1

Server ‘SERVERNAME1’ is not configured for RPC.

This means that when I set up my linked server, I disable RPC commands to and from running for security reasons.

To re-enable the RCP commands for the linked server:

exec sp_serveroption @server=’SERVERNAME1′, @optname=’rpc’, @optvalue=’true’

exec sp_serveroption @server=’SERVERNAME1′, @optname=’rpc out’, @optvalue=’true’

Note: again replace SERVERNAME1 with the name of your linked server

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

Once RPC is enabled, there are a couple of other command I like to run to find out the health of the SQL instance & its databases.

— This extended stored procedure displays operating system information regarding local attached disks:

exec SERVERNAME1.master.dbo.xp_fixeddrives

— This stored procedure displays the size of the database and how that space is currently allocated.

exec SERVERNAME1.mydb.dbo.sp_spaceused

— You can run DBCC CHECKDB and other DBCC commands over a linked server connection:

EXEC SERVERNAME1.mydb.dbo.sp_executesql N’DBCC CHECKDB’

Note: Again replace SERVERNAME1 with the name of your linked server & mydb with the name of your database

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

On a final note, remember to disable RPC when your done as a security precaution:

exec sp_serveroption @server=’SERVERNAME1′, @optname=’rpc’, @optvalue=’false’

exec sp_serveroption @server=’SERVERNAME1′, @optname=’rpc out’, @optvalue=’false’

Note: again replace SERVERNAME1 with the name of your linked server