15

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 :)

shlatchz
  • 1,612
  • 1
  • 18
  • 40
  • 1
    The whole point of using Service Fabric is easy deployment without caring about the server. LocalDB (an in-process database that stores data locally) means you actualy want to *modify* the server and get tied to it, as you won't be able to move to a different machine without losing your data. And on top of that, your code uses a db path that points to a specific user folder – Panagiotis Kanavos Apr 05 '16 at 10:10
  • 5
    @PanagiotisKanavos The LocalDB is used only for debugging. If the service fabric runs in a development environment, the LocalDB is used, and if it runs in prod, a mssql server db is used. I can't get it work locally, for debugging purposes. – shlatchz Apr 05 '16 at 12:36
  • 1
    Don't use it then. Service Fabric *won't* run under your account even locally, so it *can't* access your user directory. In fact, the error suggests that Service Fabric runs under an account that just doesn't allow profile loading, ie has no profile ie no user directory. *Why* use LocalDB at all, instead of a local installation of SQL Server? Dev edition is free – Panagiotis Kanavos Apr 05 '16 at 14:24
  • 4
    @PanagiotisKanavos I preferred a lightweight solution instead of installing sql express... If it's not possible, then I guess I'll resort to it. – shlatchz Apr 05 '16 at 14:59
  • 1) that solution would *change* the behaviour of the application. Nor would it be lighter than having a single database instance. An in-process database means that doubling processes doubles the impact. 2) at least try using a well-known path instead of a user's folder. – Panagiotis Kanavos Apr 05 '16 at 15:02
  • 3
    @PanagiotisKanavos I actually tried to specify the db's location, but it still threw the same exception. – shlatchz Apr 05 '16 at 15:28
  • @shlatchz Please see my answer here to see if it helps you http://stackoverflow.com/questions/38443001/connect-to-local-sql-server-instance-when-running-service-fabric-cluster-in-deve/38462578#38462578 – The Muffin Man Jul 19 '16 at 15:23

2 Answers2

27

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';"
TryingToImprove
  • 7,047
  • 4
  • 30
  • 39
jmservera
  • 6,454
  • 2
  • 32
  • 45
-3

Regarding IIS in service fabric, I thought you cannot host a service fabric ASP.NET app in IIS. That is why when you creating stateless ASP.NET SF project, the default hosting is Owin

alltej
  • 6,787
  • 10
  • 46
  • 87
  • 1
    The error I received isn't aware that I try to run LocalDB with Service Fabric, so its suggestion doesn't imply that SF works with IIS. – shlatchz Apr 06 '16 at 18:21
  • The question does not imply anywhere that they are using or want to use IIS. The issue that they are having is when they are running the Service Fabric cluster on their development machine they are having trouble connecting to a database hosted on the same machine. – The Muffin Man Jul 15 '16 at 22:40