<!-- wp:heading {"level":1} --> <h1></h1> <!-- /wp:heading --> <!-- wp:paragraph --> <p>Kaynak : https://sqlaj.wordpress.com/2012/03/23/cannot-drop-server-repl_distributor-because-it-is-used-as-a-distributor/</p> <!-- /wp:paragraph --> <!-- wp:paragraph --> <p>CANNOT DROP SERVER ‘REPL_DISTRIBUTOR’ BECAUSE IT IS USED AS A DISTRIBUTOR</p> <!-- /wp:paragraph --> <!-- wp:paragraph --> <p>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.</p> <!-- /wp:paragraph --> <!-- wp:image {"id":551,"linkDestination":"custom"} --> <figure class="wp-block-image"><a href="https://sqlaj.files.wordpress.com/2012/03/repl_distributor.gif"><img src="https://sqlaj.files.wordpress.com/2012/03/repl_distributor.gif?w=620" alt="" class="wp-image-551" /></a></figure> <!-- /wp:image --> <!-- wp:paragraph --> <p>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.</p> <!-- /wp:paragraph --> <!-- wp:image {"id":548,"linkDestination":"custom"} --> <figure class="wp-block-image"><a href="https://sqlaj.files.wordpress.com/2012/03/drop_replication.gif"><img src="https://sqlaj.files.wordpress.com/2012/03/drop_replication.gif?w=620" alt="" class="wp-image-548" /></a></figure> <!-- /wp:image --> <!-- wp:paragraph --> <p>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.</p> <!-- /wp:paragraph --> <!-- wp:image {"id":549,"linkDestination":"custom"} --> <figure class="wp-block-image"><a href="https://sqlaj.files.wordpress.com/2012/03/droplinkedtsql.gif"><img src="https://sqlaj.files.wordpress.com/2012/03/droplinkedtsql.gif?w=620" alt="" class="wp-image-549" /></a></figure> <!-- /wp:image --> <!-- wp:paragraph --> <p>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.</p> <!-- /wp:paragraph --> <!-- wp:image {"id":555,"linkDestination":"custom"} --> <figure class="wp-block-image"><a href="https://sqlaj.files.wordpress.com/2012/03/update_sys_servers.gif"><img src="https://sqlaj.files.wordpress.com/2012/03/update_sys_servers.gif?w=620" alt="" class="wp-image-555" /></a></figure> <!-- /wp:image --> <!-- wp:paragraph --> <p>Oops. That is not allowed here. Reading more on Remote Servers and Linked Servers I found this stored procedure&nbsp;<strong>sp_serveroption</strong>. It sets server options for remote servers and linked servers and can be used. You can use the following code.</p> <!-- /wp:paragraph --> <!-- wp:image {"id":556,"linkDestination":"custom"} --> <figure class="wp-block-image"><a href="https://sqlaj.files.wordpress.com/2012/03/exec_sp.gif"><img src="https://sqlaj.files.wordpress.com/2012/03/exec_sp.gif?w=620" alt="" class="wp-image-556" /></a></figure> <!-- /wp:image --> <!-- wp:paragraph --> <p>Checking to make sure we updated the value correctly.</p> <!-- /wp:paragraph --> <!-- wp:image {"id":554,"linkDestination":"custom"} --> <figure class="wp-block-image"><a href="https://sqlaj.files.wordpress.com/2012/03/select_from.gif"><img src="https://sqlaj.files.wordpress.com/2012/03/select_from.gif?w=620" alt="" class="wp-image-554" /></a></figure> <!-- /wp:image --> <!-- wp:image {"id":552,"linkDestination":"custom"} --> <figure class="wp-block-image"><a href="https://sqlaj.files.wordpress.com/2012/03/results.gif"><img src="https://sqlaj.files.wordpress.com/2012/03/results.gif?w=620" alt="" class="wp-image-552" /></a></figure> <!-- /wp:image --> <!-- wp:paragraph --> <p>Now we can drop the linked server with the code we scripted out originally.</p> <!-- /wp:paragraph --> <!-- wp:image {"id":550,"linkDestination":"custom"} --> <figure class="wp-block-image"><a href="https://sqlaj.files.wordpress.com/2012/03/droplinkedtsql_success.gif"><img src="https://sqlaj.files.wordpress.com/2012/03/droplinkedtsql_success.gif?w=620" alt="" class="wp-image-550" /></a></figure> <!-- /wp:image --> <!-- wp:paragraph --> <p>And confirm the linked server is gone.</p> <!-- /wp:paragraph --> <!-- wp:image {"id":557,"linkDestination":"custom"} --> <figure class="wp-block-image"><a href="https://sqlaj.files.wordpress.com/2012/03/nolinkedserver.gif"><img src="https://sqlaj.files.wordpress.com/2012/03/nolinkedserver.gif?w=620" alt="" class="wp-image-557" /></a></figure> <!-- /wp:image --> <!-- wp:paragraph --> <p>Here is a link for more information on the sp_serveroption.</p> <!-- /wp:paragraph --> <!-- wp:paragraph --> <p>http://msdn.microsoft.com/en-us/library/ms178532.aspx</p> <!-- /wp:paragraph --> <!-- wp:paragraph --> <p>Just remember, when requirements change and you are cleaning up the server, to remove all the aspects of the functionality you are removing.</p> <!-- /wp:paragraph -->