SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deploying Database project from Visual Studio to Windows Azure SQL Database (WASD PaaS) fails due to...


Deploying Database project from Visual Studio to Windows Azure SQL Database (WASD PaaS) fails due to Master Database Login failure

Author
Message
SQL Swerver
SQL Swerver
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 529
I'm not sure if this is an Azure issue or Visual Studio(VS) issue; One of our developers is trying to deploy his DB Project from VS 2013 to his own Azure SQL Database, which is hosted in the same Azure Subscription and Azure Server as our other Development Databases.

The Developer is a member of db_owner role on the target User database and has a Login on Master DB.

The Test-Connection button in VS succeeds prior to Deployment.

When the Developer clicks "Generate Script" the action fails with "Login to Master Database fails" error...weird. The Developer can otherwise connect to his database and view Data Definition of all objects (as db_owner should).

I'm trying to recreate the problem using various SQL Users and Logins but I'll post my findings here.

(Addendum: an extra dimension to this issue is it's coming from Git Source Control).
SQL Swerver
SQL Swerver
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 529
I am able to re-create this issue, if I use the Azure Subscription Administrator account, it works fine, if I use a Master Database Login which is db_owner on the target User database but only a Login on Master, I get the same failure: (attached screenshot)
Attachments
Issue_DeploymentFailure.jpg (10 views, 31.00 KB)
SQL Swerver
SQL Swerver
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 529
If I create a User on the Master DB for that Login I get a bit further into the Deployment and DDL actions are occuring but it still fails during the Deployment : (bearing in mind that Login/User is db_owner on the target User database).

SQL72014: .Net SqlClient Data Provider: Msg 916, Level 14, State 1, Line 1 The server principal "xxxxxxxxxx" is not able to access the database
SQL72045: Script execution error. The executed script:
SQL Swerver
SQL Swerver
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 529
After some further investigation I've discovered it is necessary to create a User on the Master database mapped to the Login on Master which is performing the VS Deployment to the User database (unless that Login is the Azure SQL Administrator).

The problem with this is it facilitates full visibility of other Databases which are also hosted on that Server to that Login. I'm currently investigating the Security ramifications of that bearing in mind it's all in the Cloud anyway.
SQL Swerver
SQL Swerver
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 529
UPDATE; I'm not sure the previous posting was the fix (ie. adding a User to Master database <-- why should you need to do that???). I've since discovered, after closer scrutiny and working with a Developer, that some settings in Visual Studio were causing the Target Database to be dropped prior to the Deployment. This was causing the problem and the deployment now works as expected.

Don't hesitate to ping me if you encounter anything similar!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search