Current security context error on Microsoft.SqlServer.Management.SMO.Transfer while transfer data to Azure SQL

Question

Lets say that my code looks like this:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
#Source server is a standars MSSQL server
$sourceServer = New-Object Microsoft.SqlServer.Management.Smo.Server("MSSQLinstance")
#Destination server is an AzureSQL instance
$destinationServer = New-Object Microsoft.SqlServer.Management.Smo.Server("AzureSQL.database.windows.net")

$sourceDatabase = $sourceServer.Databases["SourceDatabase"]
$destinationDatabase = $destinationServer.Databases["DestinationDatabase"]

$transfer = New-Object Microsoft.SqlServer.Management.Smo.Transfer($sourceDatabase)
$transfer.DestinationLoginSecure = $false

$transfer.DestinationDatabase = $destinationDatabase.Name

$transfer.DestinationLogin = "DestinationDatabaseLogin"
$transfer.DestinationPassword = "DestinationDatabasePass"


$transfer.TransferData()

After running it i get an error:

System.Data.SqlClient.SqlException (0x80131904): The server principal "DestinationDatabaseLogin" is not able to access the database "master" under the current security context. Cannot open user default database. Login failed. Login failed for user 'DestinationDatabaseLogin'. at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newP assword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, Stri ng accessToken) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection o wningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConne ction, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOp tions userOptions) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.SqlServer.Management.Smo.Transfer.TransferData() ClientConnectionId:57e76b3c-ee37-4be4-89a8-9e637faee6f7 Error Number:916,State:2,Class:14

I know that these is no way to set default database for SQL login in Azure SQL. Am I doing something wrong or $transfer.DestinationDatabase parameter doesn't work like I supouse? Anticipating the question: grant permissions to masterdb for user won't work because 'USE' statement is not supported in Azure SQL. Someone know solution?

I expect that afret using $transfer.DestinationDatabase, $transfer.TransferData() instruction will connect to SQL in context of DestinationDatabase.

Answer 1

The issue you're encountering is likely due to the fact that the SMO Transfer object tries to access the 'master' database under the current security context when connecting to the destination server. As you mentioned, Azure SQL does not support the 'USE' statement and setting the default database for a SQL login is not available.

To work around this limitation, you can use the SQL Server Integration Services (SSIS) to transfer data between SQL Server and Azure SQL Database. SSIS allows you to create a custom data migration package and specify the source and destination databases explicitly.

Here's a high-level overview of the steps to create an SSIS package for this purpose:

1. Install SQL Server Data Tools (SSDT) and create a new Integration Services Project.
2. In the Control Flow tab, add a Data Flow Task.
3. Go to the Data Flow tab and add an OLE DB Source and OLE DB Destination.
4. Configure the OLE DB Source by specifying the connection manager for the source SQL Server, selecting the source database, and choosing the desired tables or writing a custom SQL query.
5. Configure the OLE DB Destination by specifying the connection manager for the destination Azure SQL Database and selecting the destination database and table.
6. Map the columns from the source to the destination table.
7. Save and execute the SSIS package.

By using SSIS, you can have more control over the data transfer process and avoid the issues related to the SMO Transfer object when working with Azure SQL Database.