Why can't I add WORKGROUP\.. as a user in SQL Server?



I've spent over 20 hours trying to connection to my Sql Server database through my Windows Service and every time I fix exception I find another.

As a test I have

    protected override void OnStart(string[] args)
        EventLog.WriteEntry(ServiceName + " started");
        PageLoadTimer.Enabled = true;

        using(var conn = new SqlConnection("Data Source=DESKTOP-300NQR3\\SQLEXPRESS;Initial Catalog=MyDb;Integrated Security=True"))
           string q = "INSERT INTO [dbo].[PageDocument] (Html, PageType, DateAdded, DateProcessed) VALUES ('<p>Hi!</p>', 0, GETDATE(), NULL)";
           using(var comm = new SqlCommand(q, conn))

and yet when I try start the service I see in EventViewer

Service cannot be started. System.Data.SqlClient.SqlException (0x80131904): Cannot open database "LrcPageTask" requested by the login. The login failed. Login failed for user 'WORKGROUP\DESKTOP-300NQR3$'. at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey pool...

By the way, I got that connection string from Server Explorer -> Connection String in VS and my Windows Service is running under LocalSystem.

I tried adding the user WORKGROUP\DESKTOP-300NQR3$ in SQL server by doing Security -> Logins -> New Login but then got the error

Create failed for login 'WORKGROUP\DESKTOP-300NQR3$'

I'm getting so sick of trying to fix this problem that I'm going to just post a link to my entire source code https://github.com/jamkin/lrcvault/tree/master/BoardScanner

asked on Stack Overflow Dec 5, 2016 by user7127000

3 Answers


For integrated windows security to work between computers you'll need a Domain Controller and an account on the domain (known to all the computers) for authentication. If you're going to use integrated security between machines you should:-

  1. Create a domain service account. On your domain controller, open Active Directory Users and Computers, select Users, right click new User, uncheck change password at next login, check password never expires, enter a password. It's up to you what groups the account is a member of in the domain, but it doesn't need to be a member of any group.
  2. Change the log on for the windows service to use the domain service account created in step 1. On the machine hosting the windows service, go to Control Panel -> Administrative Tools -> Services -> Your Service -> Properties -> Log On.
  3. Ensure the service account on the machine hosting the Windows Service has permission to login in as a service.
  4. Add a security login for the service account to SQL Server (server -> Security -> Logins)
  5. Add a user to the database for the sql user added in step 3 (server -> LrcPageTask -> Security -> Users) and that the user has the appropriate role membership

If you do not have a Domain Controller, you would need to run the windows service and SQL Server on the same machine and use a local user account instead of a domain account, following the above steps, with a local user account instead of a domain account.

Alternatively don't use integrated security, use a SQL Login and password.

answered on Stack Overflow Dec 5, 2016 by Mick • edited Dec 5, 2016 by Mick

For a non domain / non network context, provisioning access to the database relates to the identity running the process. When the machine is connected to a domain and accessing a database on another machine, domainname\machinename$ is used in place of NT Authority\Network Service.

There are special identities associated to application pools in IIS as well, so if you are using app pool identities, those will be of the form IIS APP POOL\AppPoolname.

I've only just encountered the workgroup\servername$ error, and I am challenged by the fact that I'm being provided an error that tells me to add a login which I cannot add, and I consider this a bug.

Here's a few references on how this works for normal network contexts.

http://msdn.microsoft.com/en-us/library/ms998292.aspx http://msdn.microsoft.com/en-us/library/ms998358.aspx

answered on Stack Overflow Mar 28, 2018 by Peter Murdoch

If Windows service and SQL server are on the same machine this is a possible workaround:

Services -> Service Properties -> Login -> not with a system account, but with a local account.

answered on Stack Overflow Feb 9, 2021 by Юлия Яковенко • edited Feb 9, 2021 by Masoud Keshavarz

User contributions licensed under CC BY-SA 3.0