Cannot drop server ‘repl_distributor’ because it is used as a Distributor

We just went through our annual IG audits and several servers I manage were shown to have vulnerabilities. After further inspection into the issue it appears I was hit with “DBMS remote system credential use and access” and “Replication use and security”. Since replication was a requirement initially and has since changed I am able to clear all the replication functionality off the server.

 

When trying to drop the repl_distributor “linked” server by just right clicking on the the server and trying to delete the server the following error was produced.

Even though I figured this would fail as well I thought I would script out the linked server in a drop statement I received the following error.

A little more digging and querying the sys.servers table it appears the “is_distributor” column is set to 1 indicating it is still being used in replication and the source of the error. Keep in mind I have already deleted the publication and subscription (on a different server). These are just the remnants of the distribution and need to be cleaned up. So let’s try and update the table and set the value to 0.

Oops. That is not allowed here. Reading more on Remote Servers and Linked Servers I found this stored procedure sp_serveroption. It sets server options for remote servers and linked servers and can be used. You can use the following code.

Checking to make sure we updated the value correctly.

Now we can drop the linked server with the code we scripted out originally.

And confirm the linked server is gone.

Here is a link for more information on the sp_serveroption.

http://msdn.microsoft.com/en-us/library/ms178532.aspx

Just remember, when requirements change and you are cleaning up the server, to remove all the aspects of the functionality you are removing.

Cheers!

 

 

 

 

 

 

 

Advertisements

One comment

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