CODEWITHSUNDEEP
azureazure-sql-databaseazure-mobile-services
AR.
AR.

Reputation: 40495

What permissions does Azure Mobile Services use to access an Azure SQL Database with the mssql object?

I have set up an Azure Mobile Service (AMS) that's associated with an Azure SQL database, as usual. However, when I try to use a custom api to query another table (NOT a mobile services table) with the custom API mssql object, I get a permissions error:

Error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'zwxABOesblahblahHYzLogin'.

Some things to note:

The AMS api script is very basic:

exports.get = function(request, response) {
    var mssql = request.service.mssql;
    var sql = "select * from abc.TestTable";

    mssql.query(sql, {
        success : function(results) {
            console.log("Results from SQL Query to TestTable:\n"+results);
            response.send(statusCodes.OK, results);    
        },
        error: function(err) {
            console.log("Error in SQL Query to TestTable:\n"+err);
            response.send(statusCodes.Error,err.message);
        }

    });

};

So to my question(s)... what credentials are used by AMS to access the SQL database? How can I change permissions so that the script above just works (as implied by all the docs I've seen!). Or am I stuck with having to pass a connection string as suggested by this question.

Thanks!

Upvotes: 2

Views: 1319

Answers (1)

MikeWo
MikeWo

Reputation: 10985

When you create a Mobile Service it generates the SQL Database backend, or connects to an existing SQL database. When it does this is creates a SQL Login user with a random name. In your case the user was 'zwxABOesblahblahHYzLogin'. When you dropped and recreated your database you lost this user having access to the database (which I think you already knew).

To determine the permissions that were assigned to the created user I created a new Mobile Service and I then used SQL Management Studio to script the entire database (I modified the scripting options to ensure the permissions would be included in the script). I then trimmed it down to just what pertained to the user and the schema. If you already recreated your schema you can skip that part.

CREATE USER [zwxABOesblahblahHYzLogin] FOR LOGIN [zwxABOesblahblahHYzLogin] WITH DEFAULT_SCHEMA=[abc]
GO
GRANT CONNECT TO [zwxABOesblahblahHYzLogin] AS [dbo]
GRANT CREATE TABLE TO [zwxABOesblahblahHYzLogin] AS [dbo]
CREATE SCHEMA [abc]
GRANT CONTROL ON SCHEMA::[abc] TO [zwxABOesblahblahHYzLogin] AS [dbo]

From this it looks like the AMS user is granted a login in the database, Connect permissions, create table permissions and then granted control of the schema as DBO.

I test this by dropping a mobile service then recreating it which I think would put us in the same scenario.

Upvotes: 3

Related Questions