question

JunkerJens-6563 avatar image
0 Votes"
JunkerJens-6563 asked JunkerJens-6563 answered

SSMA connect to Azure SQL permission issue

Hi,
I wan't to migrate from Oracle to an Azure SQL database with the SQL Server Migration Assistant for Oracle.

I've created a database in Azure and can connect to this database with the Management Studio and the Azure Data Studio. Now I wan't to connect to this database with the Migration Assistant but after the click on connect I receive the message:

"You do not have permission to use the specified default database."

'[myazuredatabase].[dbo]' will be used for the default schema mapping.


And in the tree I can see that I'm connected to the Azure SQL server and I can see the master database and schemas but for [myazuredatabase] I see nothing only a warning 'Current user has no access to the database'.

The use I use is the one which is created in azure during database setup. I also tried the following steps without success: https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/

Has anyone an idea to solve this issue?

Thanks

azure-sql-databasesql-server-migration-assistant
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello,

Make sure you select "Azure SQL Database" as your target when you create a new project in SSMA. Also, check that default database for the user is set correctly.

Finally, it would be helpful to collect debug logs from SSMA. Navigate to Tools -> Global settings -> Logging and switch all to debug. Path to the log file will be displayed in the same dialog.

Regards,
Alex.

1 Vote 1 ·
JunkerJens-6563 avatar image
1 Vote"
JunkerJens-6563 answered

Hi Alex,
thank you very much you helped me a lot! I was blind I have choosen the wrong target I had Azure SQL Managed Database instead of Azure SQL Database. With this change the migration worked like charme.

Have a nice weekend
Jens

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered JunkerJens-6563 commented

Please add the SQL login you are using to connect to the Azure SQL database to the dbmanager role as shown below T-SQL code:

 CREATE LOGIN login1 WITH password='<ProvidePassword>';
    
 EXEC sp_addrolemember 'dbmanager', 'login1';




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Alberto,
thanks with the roles everything is fine.

Best regards
Jens

1 Vote 1 ·