locked
Dropping a Linked Server does not drop associated Logins

    Question

  • Hello,

    I have a SQL Server 2005 Server Project in which some linked servers and linked server logins are defined.  If the linked servers do not exist all is well and they are created.  However, if I modify a Linked Server to point it at a different machine VSDB tries to drop the linked server but not the logins.  The logins are defined in the project as Linked Server Logins but VSDB seems to forget that these need to be dropped as well as the linked server before it can recreate the linked server.

    Is this a bug?  Has anyone else experienced this?  My only work-around is to manually drop the linked servers on the target server.

    Any help would be appreciated.

    Thanks,

    Gareth.

    Friday, July 09, 2010 10:48 AM

Answers

  • I have taken it upon myself to write this and my employer has kindly agreed to let me open-source the result:

    http://linkedservercontrib.codeplex.com

    The above link will take you to the codeplex site where you can have a look at the extension I have written and download a nice and easy setup file should anyone else wish to use this with their teams.

    Enjoy,

    Gareth.

    • Marked as answer by Gareth CPW Monday, July 26, 2010 12:06 PM
    • Edited by Gareth CPW Wednesday, September 01, 2010 8:37 AM made link clickable
    Monday, July 26, 2010 12:05 PM

All replies

  • Hi Gareth,

    The behavior is by design.  We treat other dependent objects similarly. For example, when you remove a table, we dont automatically removed indexes and contstraints on that table from within the project. Doing so would make the assumption the table is not about to be added back, but with small changes. Having said that, if you delete from Schema View you will find we do delete some depedent objects, but not all. Deleting a linked server from schema view should delete the link server logins, but not the logins.  Currently that is not the case, but we should change that.

    Thanks,


    Barclay Hill Program Manager Visual Studio Data Tools (DataDude, DBPro, Database Edition, Database Projects, VS Data Tools) Please mark the responses as the answer if it resolves your question/issue. http://blogs.msdn.com/bahill
    Friday, July 09, 2010 9:34 PM
    Moderator
  • Hi Barclay,

     

    Thanks for the reply.  I understand what you are saying but I think that I may not have made myself completely clear in the first place.  I am not trying to drop a linked server, I am trying to repoint it.  VSDB decides the only way to action this request is to drop the linked server.  However, it does not seem intelligent enough to work out that to perform the modification that it also needs to drop the linked logins, recreate the linked server and then recreate the logins.  I would say that is my intent.  In fact in its current state, VSDB would seem to allow only the creation of a linked server and nothing else.  There is no way I can modify the linked server once linked logins are applied.  Can you confirm this is the case?  Is this by design?

     

    Thanks again,

     

    Gareth.

    Monday, July 12, 2010 8:07 AM
  • Ah, I see, your question was in the context to deployment not within the project system. Yes, this is a limitation of the deployment engine.  This has been on the product backlog, but has not made it into the product. It hasnt had a high priority as it is believed this type of change does not happen frequently. The DBA or Server OPs typically manage this through SSMS or console.

    The work around is to drop the linked server logins and linked server in the predeployment script. This allow the deployment engine to create new.

    If you would like to see this added to the product please submit this feedback through MSConnect and I will attach it to the backlog item.

    You can submit the issue through: http://connect.microsoft.com/VisualStudio/Feedback

    Thanks,


    Barclay Hill Program Manager Visual Studio Data Tools (DataDude, DBPro, Database Edition, Database Projects, VS Data Tools) Please mark the responses as the answer if it resolves your question/issue. http://blogs.msdn.com/bahill
    • Marked as answer by Gareth CPW Thursday, July 15, 2010 10:47 AM
    • Unmarked as answer by Gareth CPW Thursday, July 15, 2010 12:55 PM
    Monday, July 12, 2010 3:57 PM
    Moderator
  • Barclay,

    Once again, thanks for the reply.  I have created a connect "Feedback", I assumed that this was Feedback rather than a bug?  If it should be raised as a bug then let me know and I will do it again.

    Assuming I have done the correct thing, here is the link to the connect issue: Connect Issue

    Thanks for the help,

    Gareth.

    • Edited by Gareth CPW Thursday, July 15, 2010 10:47 AM Unforgivable spelling error
    Thursday, July 15, 2010 10:46 AM
  • Barclay,

    I must admit I was a bit hasty in marking your reply as the answer.  I too believed that a simple pre-deployment script to drop all the linked servers would suffice.  Whilst not ideal it would be minimal overhead to keep this file maintained.  However, dropping all of the linked servers in the pre-deployment script will not cause VSDB to re-create them.   This is because the schema comparison occurs before the deployment script is constructed.  As such, the comparison detects the Linked Servers exist and does not create the statements to create them.  What happens then is that every other deployment succeeds :)

    Let me explain:

    Deploy 1 to virgin database:

    1) Linked servers dropped (nothing is dropped in reality as they have not been created yet) -- Pre-deploy

    2) Generated script creates servers and logins

    Deploy succeeds.

    Deploy 2

    1) Linked servers dropped (all the linked server managed by this project are dropped) -- Pre-deploy

    2) Generated scripts tries to create the logins <-- Error the servers do not exist

    Deploy fails.

    Deploy 3

    1) Linked server dropped (nothing is dropped in reality as they dropped in the previous deploy) -- Pre-deploy

    2) Generated script creates servers and logins

    Deploy succeeds.

    So the pre-deployment script is not the solution.  The solution appears to be the creation of a deploy contributor to sort out this lot.  Basically whenever it finds a drop linked servers statement add the drop logins parameter to the end.

    What do you think Barclay?  Is this my only option?  Thankfully I have your code for the extension you wrote to replace the SQLCMD vars and I also have the code to another contributor I wrote so we don't have to worry about GRANT statements.  But I would be interested if there was a solution that was not as extreme.

    All thoughts and comments would be appreciated.

    Thanks,

     

    Gareth.

    Thursday, July 15, 2010 1:04 PM
  • I have taken it upon myself to write this and my employer has kindly agreed to let me open-source the result:

    http://linkedservercontrib.codeplex.com

    The above link will take you to the codeplex site where you can have a look at the extension I have written and download a nice and easy setup file should anyone else wish to use this with their teams.

    Enjoy,

    Gareth.

    • Marked as answer by Gareth CPW Monday, July 26, 2010 12:06 PM
    • Edited by Gareth CPW Wednesday, September 01, 2010 8:37 AM made link clickable
    Monday, July 26, 2010 12:05 PM