Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deploying Database project from Visual Studio to Windows Azure SQL Database (WASD PaaS) fails due to Master Database Login failure Expand / Collapse
Author
Message
Posted Thursday, August 14, 2014 2:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 4:38 AM
Points: 100, Visits: 468
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).
Post #1603167
Posted Thursday, August 14, 2014 4:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 4:38 AM
Points: 100, Visits: 468
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)

  Post Attachments 
Issue_DeploymentFailure.jpg (2 views, 31.07 KB)
Post #1603202
Posted Thursday, August 14, 2014 4:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 4:38 AM
Points: 100, Visits: 468
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:

Post #1603208
Posted Friday, August 15, 2014 4:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 4:38 AM
Points: 100, Visits: 468
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.
Post #1603626
Posted Friday, September 5, 2014 7:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 4:38 AM
Points: 100, Visits: 468
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!
Post #1610896
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse