2

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
    Commented Oct 12, 2020 at 11:28

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.