none
Database deployment using VSDBCMD.EXE is successful only when i have explicit rights on the databases directly? why?

    Question

  • Hi,

    I am implementing successful database deployments INVOKING vsdbcmd.exe in TFS build template.

    - Permissions on Target database: TFS Build service account has explicitly added to security group with DBO access on the databases

    Recently,our Security team started working on finding alternative and decided to add the Service account to a AD group which have same DBO permissions on all the databases to a set of databases. 

    The Interesting part is, i see that database deployment is successful only when "service account" have explicit access to Databases but should not be added to AD group which has same DBO access to the database

    Is it mandatory that service accounts should not be added to a AD group? it should only be added explicitly to particular??

    Please let me know if it is true so i can prove my security team that this is how it should be

    Thursday, October 31, 2013 12:33 PM

All replies

  • What version of SQL Server is this?  What is the error when the service account is only in a group?

    <guessing>

    I suppose deployment could depend on the default schema of the user deploying the database, and and you can't set a default schema for a Windows group before SQL 2012.

    </guessing>

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, October 31, 2013 2:07 PM
  • SQL SERVER: 2008 R2

    TFSBuild automation Error when the service account added to group:

     *** Failed to import target model Databasename. Detailed message Cannot open database "Databasename" requested by the login. The login failed.
     Login failed for user 'CORP\TFS-BUILD01'.

    Thursday, October 31, 2013 2:37 PM
  • That looks like the user really doesn't have access to the database.  If you can, try logging in with that user through Management Studio or SQLCMD; you should see the same error.

    Also double check the security configuration, and perhaps drop and recreate the login and the database user for the group that TFS-BUILD01 is in.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, October 31, 2013 2:48 PM
  • No, here is the tricky part.

    when i login build server with the service account (TFS-BUILD01) and run the script manually or from SSMS it is working well..no issues and no errors. But the issue is only when i did the automation.

    TFS automation Fails when TFS-BUILD01 service account added to a AD group which has DBO rights (even though it is successful when running the script manually).

    TFS automation succeeded when TFS-BUILD01 service account added explicitly to the database which has DBO rights



    • Edited by Kumar Raju Thursday, October 31, 2013 3:26 PM
    Thursday, October 31, 2013 3:06 PM
  • That specific error message means that the login requested a specific database, instead of connecting first to Master and then switching databases.  Can you try connecting directly to the database in from the build server as TFS-BUILD01?

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, October 31, 2013 3:25 PM
  • Yes i can successfully connect to database in SSMS using TFS-BUILD01
    Thursday, October 31, 2013 4:33 PM
  • Can you find the logon failure message in the SQL Error log?  Perhaps that has some better information.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, October 31, 2013 5:14 PM
  • TFSBuild automation Error when the service account added to group: *** Failed to import target model Databasename. Detailed message Cannot open database "Databasename" requested by the login. The login failed.
     Login failed for user 'CORP\TFS-BUILD01'.

    Is the database actually called Databasename or are you just masking the the actual name? I ask, because it is this actual database you should try.

    And when you test as TFS-BUILD01, you are really logged into Windows as CORP\TFS-BUILD01? That is, you are not testing with a local account with the same name?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, October 31, 2013 10:24 PM