CODEWITHSUNDEEP
sql-serverentity-framework
Zenuka
Zenuka

Reputation: 3250

Entity framework connects to master database for contained database user

I have a contained database user. Since it's contained in the database it's not allowed to connect to any other database including master. Unfortunately Entity Framework seems to connect to the master database anyway.

I've created a new console app with the latest Entity Framework nuget (6.2.0) to make sure nothing else connects to the master database:

static void Main(string[] args)
{
    var connectionString = "Server=sql-azure.database.windows.net;Database='Database';User ID=Username;Password=password;Trusted_Connection=False;";
    using (var dbContext = new DbContext(connectionString))
    {
        dbContext.Database.CommandTimeout = 10 * 60;
        dbContext.Database.ExecuteSqlCommand("EXEC cleanup @Date", new SqlParameter("@Date", DateTime.UtcNow.AddMonths(-3)));
    }
}

How do I force Entity Framework to not connect to the master database? I get failures in the audit logs on the master database which causes azure threat detection to go off.

Upvotes: 2

Views: 2317

Answers (3)

Justin Tolchin
Justin Tolchin

Reputation: 463

The below would work for MS SQL on premises as well as if you are on SQL Azure:

use master;    
CREATE LOGIN DemoUser WITH PASSWORD = 'DemoPassword';
GO
CREATE USER DemoUser FOR LOGIN DemoUser
GO

use DemoDB;
CREATE USER DemoUser FOR LOGIN DemoUser WITH DEFAULT_SCHEMA=[dbo]
GO

ALTER ROLE db_datareader ADD MEMBER DemoUser; 
ALTER ROLE db_datawriter ADD MEMBER DemoUser;

Upvotes: 1

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

I can't repro that:

Contained user:

//create user joe with password ='xxxxxx'



using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;

namespace Ef6Test
{    
    class Program
    {
        static void Main(string[] args)
        {
            var connectionString = "server=xxxxxxxxx.database.windows.net;database=adventureworks;uid=joe;pwd=xxxx";
            using (var dbContext = new DbContext(connectionString))
            {
                dbContext.Database.CommandTimeout = 10 * 60;
                Console.WriteLine(dbContext.Database.SqlQuery<string>("select db_name() dbname;").Single());
            }

            Console.WriteLine("Hit any key to exit");
            Console.ReadKey();
        }
    }
}

Upvotes: 0

Zenuka
Zenuka

Reputation: 3250

After researching some more I've disabled the database initializer before the using statement like this:

Database.SetInitializer<DbContext>(null);

With this line of code, the console app doesn't connect to the master database any more. More info about Database.SetInitializer(null).

Full example:

static void Main(string[] args)
{
    var connectionString = "Server=sql-azure.database.windows.net;Database='Database';User ID=Username;Password=password;Trusted_Connection=False;";
    Database.SetInitializer<DbContext>(null);
    using (var dbContext = new DbContext(connectionString))
    {
        dbContext.Database.CommandTimeout = 10 * 60;
        dbContext.Database.ExecuteSqlCommand("EXEC cleanup @Date", new SqlParameter("@Date", DateTime.UtcNow.AddMonths(-3)));
    }
}

Upvotes: 2

Related Questions