1

I'm attempting to install xConnect OnPrem with Azure SQL. I've updated the create user scripts with the following -

GO

CREATE USER [PlaceHolderForUser] WITH PASSWORD = 'PlaceHolderForPassword';
GO

EXEC sp_addrolemember 'db_datareader', [PlaceHolderForUser];
EXEC sp_addrolemember 'db_datawriter', [PlaceHolderForUser];
GO

GRANT EXECUTE TO [PlaceHolderForUser];
GO

When SIF gets to the CreateShardManagerApplicationDatabaseUserInvokeSqlCmd : InvokeSqlcmd task it fails as the .sql script has a USE statement within it which is not compatible with Azure SQL.

[--- CreateShardManagerApplicationDatabaseUserInvokeSqlCmd : InvokeSqlcmd --------------------------------------------]
Install-SitecoreConfiguration : USE statement is not supported to switch between databases. Use a new connection to
connect to a different database.
 Msg 40508, Level 16, State 1, Procedure , Line 1.
At C:\resourcefiles\XConnect.ps1:81 char:1
+ Install-SitecoreConfiguration @xconnectParams *>&1 | Tee-Object XConn ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Install-SitecoreConfiguration
[TIME] 00:15:26
Invoke-Sqlcmd : USE statement is not supported to switch between databases. Use a new connection to connect to a
different database.
 Msg 40508, Level 16, State 1, Procedure , Line 1.
At C:\Program
Files\WindowsPowerShell\Modules\SitecoreInstallFramework\2.3.0\Public\Install-SitecoreConfiguration.ps1:641 char:25
+                         & $entry.Task.Command @paramSet | Out-Default
+                         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

SQL Script it tries to run

USE [$(ShardDatabaseName)];

IF NOT EXISTS
(
    SELECT *
    FROM sys.database_principals
    WHERE name = N'$(UserName)'
)
BEGIN
    CREATE USER [$(UserName)] FOR LOGIN [$(UserName)];
    EXEC [xdb_collection].[GrantLeastPrivilege] @UserName = '$(UserName)';
END;

Is there another way of using this script, or can I skip this task within SIF & create the users after installation is complete?

Thanks

1
  • I've skipped the following sql scripts for now in the json file - - CreateShardManagerApplicationDatabaseUserInvokeSqlCmd - CreateShard0ApplicationDatabaseUserInvokeSqlCmd - CreateShard1ApplicationDatabaseUserInvokeSqlCmd Until anyone can help - I'll run these manually on complete install. – Tom Simm Oct 12 '20 at 11:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.