Error attaching databases when installing Sitecore 9 instance

[jfm5248]

Description:
When installing a Sitecore 9 instance, an error occurs at the "Attaching databases" step. The following exception is logged:

SIM.Adapters.SqlAdapterException: Failed to perform an operation with SqlServer ---> System.TypeInitializationException: The type initializer for 'Microsoft.SqlServer.Dac.DacPackage' threw an exception. ---> System.TypeInitializationException: The type initializer for 'Microsoft.SqlServer.Dac.DacServices' threw an exception. ---> System.TypeInitializationException: The type initializer for 'SqlSchemaModelStaticState' threw an exception. ---> System.IO.FileLoadException: **Could not load file or assembly 'Microsoft.SqlServer.TransactSql.ScriptDom, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies.** The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlCoreAnnotationRegister.RegisterModelAnnotations(ModelSchema storeSchema)
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModel.SqlSchemaModelStaticState.RegisterModelSchema()
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModel.SqlSchemaModelStaticState..cctor()
   --- End of inner exception stack trace ---
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModel.SqlSchemaModelStaticState.get_ModelSchema()
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModel.InitializeModelSchema()
   at Microsoft.SqlServer.Dac.DacServices.Initialize()
   at Microsoft.SqlServer.Dac.DacServices..cctor()
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Dac.DacServices.Initialize()
   at Microsoft.SqlServer.Dac.DacPackage..cctor()
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Dac.DacPackage.Load(String fileName)
   at SIM.Adapters.SqlAdapter.DeployDatabase(String databaseName, IFile bacpac)
   --- End of inner exception stack trace ---
   at SIM.Adapters.SqlAdapter.DeployDatabase(String databaseName, IFile bacpac)
   at SIM.Pipelines.AttachDatabasesHelper.AttachDatabase(String name, String sqlPrefix, Boolean attachSql, String databasesFolderPath, ConnectionString connectionString, SqlConnectionStringBuilder defaultConnectionString, IPipelineController controller)
   at SIM.Pipelines.Install.AttachDatabases.Process(InstallArgs args)
   at SIM.Pipelines.Install.InstallProcessor.Process(ProcessorArgs args)
   at SIM.Pipelines.Processors.Processor.Execute(ProcessorArgs args)

Scenario:

1. Load SIM QA 1.5.0.181 using ClickOnce installer
2. Attempt to install Sitecore 9 instance

3. Get the error

4. Clone SIM 'develop' repo
5. Build in Visual Studio 2017
6. Run Sitecore-Instance-Manager\src\SIM.Tool\bin\SIM.Tool.DEV.exe
7. Attempt to install Sitecore 9 instance
8. Get error

Issue is actual
I hereby confirm the issue is new:

• all prerequisites are met.
• it is not a known issue
• it was not discussed before (I used GitHub Issues search)

Prerequisites appear to be met. The system in question:

• Windows 10 with SIM running as admin
• IIS is configured and running a Sitecore 8.2 instance installed by SIM
• Local SQL Server >2012 (2017)

I do not see this issue otherwise logged and it is not listed as a known issue.

Valid for QA
I hereby confirm the issue is reproducible on the latest QA version [SIM QA 1.5.0.181] which was downloaded today from http://dl.sitecore.net/updater/qa/sim

Screenshots:

Attachments:
Please find attached latest log files from this folders:

%APPDATA%\..\Roaming\Sitecore\Sitecore Instance Manager-QA\logs 

2018-01-04.txt 2018-01-04_DEBUG.txt

[AlenPelin]

This is strange. What SQL Server versions are installed on your computer?

[AlenPelin]

Try to install choco install ssdt15 and check if it helps.

[jfm5248]

I'll try installing SQL Server Data Tools update this thread with the results tomorrow. This is for a new machine with SQL Server 2017 installed.

[jfm5248]

Installing ssdt15 resolved the "Could not load file or assembly 'Microsoft.SqlServer.TransactSql.ScriptDom" error. An error followed that one saying it could not connect to the database. I verified the connection string, but it persisted.

The change that allowed me to complete installing Sitecore 9 was to uninstall SQL Server 2017 and go to SQL Server 2016. After that, it worked as expected.

[AlenPelin]

Thanks for checking, I will update the documentation.

[michaelbaranov]

Is there any reason why SQL 2017 is not supported? My gut feeling says that next major Sitecore version will have it as requirement.

[neilmonroe]

Just making a note that this is still an issue in 2019 on SQL Server 2017. I was able to install Sitecore 8.2 Update 5 without an issue on SQL Server 2017.

[KayeeNL]

@AlenPelin I still have this issue with SQL Server 2017 Developer edition and installing the Sitecore 9 instances....

23 14:00:53,463 INFO Processor of type 'SIM.Pipelines.Install.AttachDatabases' failed. Failed to perform an operation with SqlServer SIM.Adapters.SqlAdapterException: Failed to perform an operation with SqlServer ---> Microsoft.SqlServer.Dac.DacServicesException: Could not deploy package. ---> Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: Unable to connect to master or target server 'sc901_core'. You must have a user with the same password in master or target server 'sc901_core'. at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentEndpointServer.OnInit(ErrorManager errors, String targetDBName) at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment..ctor(SqlDeploymentConstructor constructor) at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentConstructor.ConstructServiceImplementation() at Microsoft.SqlServer.Dac.DacServices.CreatePackageToDatabaseDeployment(String connectionString, IPackageSource packageSource, String targetDatabaseName, DacDeployOptions options, ErrorManager errorManager) at Microsoft.SqlServer.Dac.DeployOperation.<>cDisplayClass3.<>cDisplayClass5.b1() at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action) at Microsoft.SqlServer.Dac.DeployOperation.<>cDisplayClass3.b__0(Object operation, CancellationToken token) at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) --- End of inner exception stack trace --- at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken) at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext) at Microsoft.SqlServer.Dac.DacServices.Deploy(DacPackage package, String targetDatabaseName, Boolean upgradeExisting, DacDeployOptions options, Nullable`1 cancellationToken) at SIM.Adapters.SqlAdapter.DeployDatabase(String databaseName, IFile bacpac) --- End of inner exception stack trace --- at SIM.Adapters.SqlAdapter.DeployDatabase(String databaseName, IFile bacpac) at SIM.Pipelines.AttachDatabasesHelper.AttachDatabase(String name, String sqlPrefix, Boolean attachSql, String databasesFolderPath, ConnectionString connectionString, SqlConnectionStringBuilder defaultConnectionString, IPipelineController controller) at SIM.Pipelines.Install.AttachDatabases.Process(InstallArgs args) at SIM.Pipelines.Install.InstallProcessor.Process(ProcessorArgs args) at SIM.Pipelines.Processors.Processor.Execute(ProcessorArgs args)

Seems that the user needed isn't generated correctly...

[AlenPelin]

@KayeeNL try fix from this PR https://github.com/Sitecore/Sitecore-Instance-Manager/pull/236

[fluxdigital]

Hi @AlenPelin with v1.5 I'm still having this issue with SQL2017 that @KayeeNL is having. I've cloned the repo and checked out the develop branch but I'm struggling to get a local build working with the powershell scripts. Is there a beta release of this anywhere that has this fix that I could test please?

[AlenPelin]

@fluxdigital this PR has been merged recently, so simply run SIM beta with this link: https://dl.sitecore.net/updater/qa/sim/

[fluxdigital]

Thanks @AlenPelin. I've managed to install Sitecore 9 update 2 fine and with this version of SIM and it seems to be working fine. One issue I had though was the download instance feature didn't seem to work. It asked for my creds and looked like it was authenticating and then just said 'done' and I had to close the popup and nothing happened. I had to copy over the 9u2 zip from SIM 1.5's repo folder and then the install worked ok. Anyone else had this issue?

[AlenPelin]

Could you register it separately? It's a bit off topic.

On Fri, 5 Jul. 2019, 20:03 Adam Seabridge, notifications@github.com wrote:

Thanks @AlenPelin https://github.com/AlenPelin. I've managed to install Sitecore 9 update 2 fine and with this version of SIM and it seems to be working fine. One issue I had though was the download instance feature didn't seem to work. It asked for my creds and looked like it was authenticating and then just said 'done' and I had to close the popup and nothing happened. I had to copy over the 9u2 zip from SIM 1.5's repo folder and then the install worked ok. Anyone else had this issue?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Sitecore/Sitecore-Instance-Manager/issues/218?email_source=notifications&email_token=AAVY6CSJ62HX6QFVWHIMMI3P54L7RA5CNFSM4EKNCCFKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODZJD5RY#issuecomment-508706503, or mute the thread https://github.com/notifications/unsubscribe-auth/AAVY6CTCHJVY7SCSEUQHWLDP54L7RANCNFSM4EKNCCFA .