Using LocalDB with Service Fabric

Question

I have an Actor that upon receiving a request from a WebAPI project, the Actor queries a table using Entity Framework 6.

using (var context = new MetadataContext())
{
    var userStorageAccountId = context.Set<UsersToStorageAccounts>()
                                      .Find(userId).StorageAccountId;
}

The DB was successfuly created using the "Add-Migration" .. "Update-Database" commands, and has the following connection string:

@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFileName=C:\Users\xxxx\Metadata.mdf;Connect Timeout=30;Initial Catalog=Metadata;Integrated Security=True;"

When I run the fabric service and the actor tries to access the context, I get the following exception:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Cannot create an automatic instance. See the Windows Application event log for error details.)

and the event viewer error is:

Cannot get a local application data path. Most probably a user profile is not loaded. If LocalDB is executed under IIS, make sure that profile loading is enabled for the current user.

I looked for a way to load the user's profile, but I only found solutions for IIS.

Please help :)

Answer 1

As service fabric runs under another user, you need to share the database and give the NETWORK SERVICE permissions:

1. With the SqlLocalDB.exe command line share your connection issuing this command:

   sqllocaldb share MSSqlLocalDB SharedDB
   

2. Open the LocalDB with SQL Server Management Studio and go to the /Security/Logins, add the NETWORK SERVICE local account and in User Mapping add it as dbo (dbo.dbowner) to your database

3. Use the shared name in your connection string like this:

   "Data Source=(localdb)\.\SharedDB;Initial Catalog=[YOUR DB];Integrated Security=SSPI;"
   

Edit Here you have a script I've added to my projects, change databasename with the name of your db:

sqllocaldb create MSSQLLocalDB
sqllocaldb start MSSQLLocalDB
sqllocaldb share mssqllocaldb sharedlocal
sqllocaldb stop MSSQLLocalDB
sqllocaldb start MSSQLLocalDB

"c:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE" -S "(localdb)\.\sharedlocal" -d "databasename" -Q"create login [nt authority\network service] FROM windows with DEFAULT_DATABASE=databasename;use databasename;exec sp_addrolemember 'db_owner', 'nt authority\network service';"