Connect to local SQL Server instance when running Service Fabric cluster in development environment

Question

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.

Answer 1

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.

Answer 2

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.