TFS 2017 build error

saleek

Hi,

I created a build as per the documentation, I added the MSBuild arguments as follows:

/p:TargetServer="$(TargetServer)" /p:ShadowServer="$(ShadowServer)" /p:TargetDatabase="$(TargetDatabase)" /p:GenerateSqlPackage=True /p:ReportStyle=Simple

During the Deployment of the Shadow database, I received the following error:

MyDBMigrations001_20170104-1148_JohnDoe.sql(0,0): Error : Msg 18456, Level 14, State 1, Server TORSQL2GEN, Line 1
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'

Looking at the shadow DB that is created I can see that the user dbo actually maps to the TFSBuild service account, which is rightly creating the database I guess.

However, I'm guessing that the service account does not have access to the linked server TORSQL2GEN. So, I added the build service account to the linked server security, but the result is still the same error.

Your help appreciated.

thanks,

KS

dnlnln

Are the two SQL Server instances (i.e. the TargetServer plus the server you're connecting to via the linked server reference) on the same machine? If not, then it's possible that the "NT AUTHORITYANONYMOUS LOGON" account will not be able to cross the machine boundary.

An alternative is to use SQL Server Authentication as part of your MSBuild arguments, by passing the ShadowUsername and ShadowPassword properties. For example:

/p:TargetServer="$(TargetServer)" /p:ShadowServer="$(ShadowServer)" /p:TargetDatabase="$(TargetDatabase)" /p:GenerateSqlPackage=True /p:ReportStyle=Simple /p:ShadowUsername="$(ShadowUsername)" /p:ShadowPassword="$(ShadowPassword)"

Note that the SQL auth login you use may need to be granted access on the linked server instance as well, using the same SID as on the calling instance.

saleek

Thanks, that resolves the issue or me.

Do we have to manually remove the shadow copies after each build/deployment?

thanks,

KS

dnlnln

Apologies for the delay in replying. I'm very glad to hear that resolved the issue.

There isn't a requirement for you to remove the Shadow copies after each build; ReadyRoll will automatically drop and recreate the Shadow database as needed.

However, if you would like the Shadow copy to be removed, you could include a call to drop the database the database after the build. This can be done by adding a command to the Post-build event (which can be found on the Build Events tab in the project designer). For example:

IF "$(ShadowServer)" NEQ "" sqlcmd -S "$(ShadowServer)" -Q "IF (DB_ID(N'$(FinalShadowDatabase)') IS NOT NULL) DROP DATABASE [$(FinalShadowDatabase)]"