locked
Add login to sql 2012 server for a virtual account from another sql 2012 server RRS feed

  • Question

  • I have two sql 2012 servers. 

    Server A has sql server agent running virtual account: NT Service\SQLSERVERAGENT

    Server B has some databases.

    Server A is running a scheduled SSIS package that needs access to Server B's databases via Windows Auth

    Thus I want to grant access by creating a login on Server B to the virtual account on server A: NT Service\SQLSERVERAGENT

    Q1 - Is this possible?

    Thanks beforehand.


    Paulino

    Tuesday, October 7, 2014 2:25 PM

Answers

  • Hello,

    Local account do not span computer objects (hence why it is a local account). While the computer object could be added for authentication, I would not do this.

    The best solution would be to use a domain account, or if that can't be completed then setup the SSIS package to use SQL Server Authentication.


    Sean Gallardy | Blog | Microsoft Certified Master

    Tuesday, October 7, 2014 4:51 PM

All replies

  • Unless I misunderstood your question, You need to create a login in Server B. You then need to run the SSIS packages in Server A and you can use the login created in sevrer B to access.

    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, October 7, 2014 3:27 PM
  • Hello,

    Local account do not span computer objects (hence why it is a local account). While the computer object could be added for authentication, I would not do this.

    The best solution would be to use a domain account, or if that can't be completed then setup the SSIS package to use SQL Server Authentication.


    Sean Gallardy | Blog | Microsoft Certified Master

    Tuesday, October 7, 2014 4:51 PM
  • You are correct. Thanks for the clarification

    Paulino

    Tuesday, October 7, 2014 4:59 PM
  • Mr. Gallardy, thanks for your solution. Indeed those are great alternate options.

    I am new to virtual accounts and so I just wanted to check if my posted scenario was possible or not.

    I appreciate your time to reply.


    Paulino

    Tuesday, October 7, 2014 5:04 PM
  •  As Sean mentioned, and unless I am mistaken, the virtual service accounts should be able to access external resources by using the machine account (computer object) that takes the form: DOMAIN\MACHINE_NAME$ the same way network service & local service would.

      Notice that there is no way to distinguish that the connection is really from the service you expect, only that it is a service from that machine.

      I also agree with Sean’s recommendation of using a specific domain account for this service instead.

      I hope this information helps,

    -Raul Garcia

       SQL Server Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, October 7, 2014 5:06 PM
  • Raul,

    As a matter of a test and not with the actual intention to use it this way, I did try to add a login using the DOMAIN\MACHINE_NAME$ concept but it did not work anyway. I will not find it.

    So I will use one of the approaches recommended by Mr. Gallardy

    Thanks!


    Paulino

    Tuesday, October 7, 2014 7:57 PM