August 14, 2014 at 2:59 am
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).
August 14, 2014 at 4:22 am
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)
August 14, 2014 at 4:47 am
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:
August 15, 2014 at 4:19 am
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.
September 5, 2014 at 7:44 am
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!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply