Sitecore installation error - Dacpac file path: Instance failure. System.InvalidOperationException:

Question

I'm trying to install Sitecore v9.1 update 1 with Sitecore SIF. I did install all the prerequisites. Everything looks fine. Also the script does install 7 databases (like MarketingAutomation, Messaging,..) while it is running. However, it does not install the remaining databases. I'm getting an error.

SIF installation Error:

[--------------------------------------------------------------------------------------- XConnectXP0_CleanShards : Command -------------------------------------------------------------------------------------------------------]
[ShardingTool - Clean]:[Path] C:\inetpub\wwwroot\sc721.xconnect\App_Data\collectiondeployment\Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe
    *** Welcome to Sitecore xDB Collection SQL Sharding Deployment Tool ***
    Tool was run with the following configuration:
    Operation: drop
    SQL server connection:
    Server name: DESKTOP-8V406HU\\MSSQLSERVER01
    User: sa
    Integrated security: False
    Connect Timeout: 15
    Database edition: Basic
    Shard map manager database name: sc721_Xdb.Collection.ShardMapManager
    Shard map names: ContactIdShardMap, DeviceProfileIdShardMap, ContactIdentifiersIndexShardMap
    Shard number: 0
    Shard name prefix: 
    Shard name suffix: 
    Dacpac file path: 
Instance failure. System.InvalidOperationException: Instance failure.
   at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boo
lean withFailover, Boolean isFirstTransparentAttempt, SqlAuthenticationMethod authType, Boolean disableTnir, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isF
irstTransparentAttempt, Boolean disableTnir)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential 
credential, TimeoutTimer timeout)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUse
rInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePasswor
d, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlA
uthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions u
serOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionI
nternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connectio
n)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.ReliableSqlConnection.<Open>b__1()
   at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.<>c__DisplayClass1.<ExecuteAction>b__0()
   at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
   at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.<>c__DisplayClass1.<ExecuteAction>b__0()
   at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
   at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.ReliableSqlConnection.Open(RetryPolicy retryPolicy)
   at Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.SqlDatabaseManager.TryConnectToSqlDatabase()
   at Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.Program.VerifyConnection(ISqlDatabaseManager sqlDatabaseManager)
   at Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.Program.Main(String[] args)

[-------------------------------------------------------------------------------------- XConnectXP0_CreateShards : Command -------------------------------------------------------------------------------------------------------]
[ShardingTool - Create]:[Path] C:\inetpub\wwwroot\sc721.xconnect\App_Data\collectiondeployment\Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe
    *** Welcome to Sitecore xDB Collection SQL Sharding Deployment Tool ***
    Tool was run with the following configuration:
    Operation: create
    SQL server connection:
    Server name: DESKTOP-8V406HU\\MSSQLSERVER01
    User: sa
    Integrated security: False
    Connect Timeout: 15
    Database edition: Basic
    Shard map manager database name: sc721_Xdb.Collection.ShardMapManager
    Shard map names: ContactIdShardMap, DeviceProfileIdShardMap, ContactIdentifiersIndexShardMap
    Shard number: 2
    Shard name prefix: sc721_Xdb.Collection.Shard
    Shard name suffix: 
    Dacpac file path: C:\inetpub\wwwroot\sc721.xconnect\App_Data\collectiondeployment\Sitecore.Xdb.Collection.Database.Sql.dacpac
Instance failure. System.InvalidOperationException: Instance failure.
   at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boo
lean withFailover, Boolean isFirstTransparentAttempt, SqlAuthenticationMethod authType, Boolean disableTnir, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isF
irstTransparentAttempt, Boolean disableTnir)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential 
credential, TimeoutTimer timeout)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUse
rInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePasswor
d, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlA
uthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions u
serOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionI
nternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connectio
n)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.ReliableSqlConnection.<Open>b__1()
   at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.<>c__DisplayClass1.<ExecuteAction>b__0()
   at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
   at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.<>c__DisplayClass1.<ExecuteAction>b__0()
   at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
   at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.ReliableSqlConnection.Open(RetryPolicy retryPolicy)
   at Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.SqlDatabaseManager.TryConnectToSqlDatabase()
   at Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.Program.VerifyConnection(ISqlDatabaseManager sqlDatabaseManager)
   at Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.Program.Main(String[] args)

Answer 1

I did solve the issue. As I mentioned everything looked good including the prerequisites and the script. It looks like that the PowersShell script has some flows. I have two SQL Server 2017 instances and the issue was related when I used the second instance "DESKTOP-8V406HU\MSSQLSERVER01". I did escape "\" this with "\" and it created seven databases except for Sitecore and xconnect. After I used the first instance "DESKTOP-8V406HU" the installation did completed without a problem.

If you have to use an SQL instance name with "instance\example" make sure to debug the script and fix this issue before running the script. If I find the real solution I will post it here.

Answer 2

I stumbled into the same issue today, as I have a current solution running on SQL Server 2017. But as we're starting a new project up we want to use 2019.

I found out my issue was because I followed the guidance in the powershell scripts from Sitecore. Being to write hostName\\InstanceName . But the tool at location "C:\inetpub\wwwroot\sc721.xconnect\App_Data\collectiondeployment\Sitecore.Xdb.Collection.Database.SqlShardingDeploymentTool.exe" - Can't handle connection strings with escaped backslashes.

Therefore I ran the installation script with my sql connection string as hostName\instanceName - All other sql connections still work doing the script.

Note I'm running SIF 2.2.0