12

When opening a sql connection to a local database in a traditional console app I have no issues, however when I attempt to do the same thing within a stateless service running in Service Fabric I get a login error.

The error I receive is "Login failed for user 'WORKGROUP\\NICK$'."

And this is the code I'm using to connect

using (var con = new SqlConnection("Server=.;Trusted_Connection=True;Database=AddressBook"))
{
            try
            {
                con.Open();
            }
            catch (Exception e)
            {

            }
}

When I try to add that user to sql server it tells me that the user cannot be found.

The Muffin Man
  • 19,585
  • 30
  • 119
  • 191
  • How are you specifying user creds? Are you relying on the user under which the process is running? If so, that will work in a console app that runs as you, but in Service Fabric the process is run under the Network Service account by default. – Vaclav Turecek Jul 18 '16 at 19:27
  • @VaclavTurecek Yes, I was relying on the user under which the process was running. I setup a new user in sql server and specified a password for it. I changed my connection string to the following with no luck. Does the server need to change as well? `Server=.;User Id=Fabric;Password=mypassword;Database=AddressBook` – The Muffin Man Jul 18 '16 at 23:03

2 Answers2

17

Based on the comments above I learned that Service Fabric is running under the NETWORK SERVICE account.

The solution is to update the User Mapping and role membership for the databases that you want to access within the SF cluster.

In SSMS expand Security, Logins, right click NETWORK SERVICE and then choose properties. Under User Mapping place a checkbox next to each Database that you want to expose access to and down below public, db_datareader/writer.

enter image description here

The Muffin Man
  • 19,585
  • 30
  • 119
  • 191
  • 2
    Alternatively, you can configure your service to run as a different user account that has access to your DB: https://azure.microsoft.com/en-us/documentation/articles/service-fabric-application-runas-security/#apply-runaspolicy-to-services – Vaclav Turecek Jul 19 '16 at 18:40
2

A comment to the accepted answer mentions running your service as a different user account. Here's how to do that. In your ApplicationManifest.xml file, insert this:

<Principals>
  <Users>
    <User Name="AcctToUse" AccountType="DomainUser"
          AccountName="MyDomain\MyUsername" Password="MyPassword" />
  </Users>
</Principals>
<Policies>
  <DefaultRunAsPolicy UserRef="AcctToUse" />
</Policies>

Edit: I didn't make it clear, AcctToUse is just a string that you make up. It's a key that points to the User. The AccountName field on the other hand is the username.

user2023861
  • 8,030
  • 9
  • 57
  • 86