How to access a SQL database from a Windows service?

Question

I am trying to develop an windows service for Windows 10 (64bit), which intends to insert record in local MS SQL Server (v.11) periodically.

It installed and ran successfully, but no record is inserted. I tried both System.Timers.Timer and System.Threading.Timer.

I also tried to insert on service start event. But that also didn't work.

Here is my code:

public partial class Service1 : ServiceBase
{
    // System.Timers.Timer timer;
    System.Threading.Timer threadTimer;
   
    public Service1()
    {
        InitializeComponent();
    }


    //UPDATE: I checked the following method code in a console application. It works fine.
    private void InsertRecord()
    {
        try
        {
            using (SqlConnection connection = new SqlConnection("Server=.;Database=TestDb; Trusted_Connection=True;"))
            {
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = connection;
                    command.CommandText = "Insert into .....')";
                    connection.Open();
                    command.ExecuteNonQuery();
                    connection.Close();
                }
            }
        }
        catch (Exception ex)
        {
        }
    }

    protected override void OnStart(string[] args)
    {
        InsertRecord(); //This line not executed also.

        TimeSpan tsInterval = new TimeSpan(0, 2, 0); //2 minute interval.
        threadTimer = new Timer(new TimerCallback(threadTimer_Elapsed)
            , null, tsInterval, tsInterval);


        //timer = new System.Timers.Timer();
        //timer.Interval = 10000;
        //timer.Elapsed += Timer_Elapsed;
        //timer.Enabled = true;
        //timer.Start();
    }

    private void threadTimer_Elapsed(object state)
    {
         InsertRecord();
    }

    //private void Timer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
    //{
    //      InsertRecord()
    //}

    protected override void OnStop()
    {
        threadTimer.Change(Timeout.Infinite, Timeout.Infinite);
        threadTimer.Dispose();
        threadTimer = null;

        //timer.Stop();
    }
}

What did I miss here?

Answer 1

You did not provide a lot of information about your connection string, but as @NightOwl888 pointed out, the problem may have to do with your use of Trusted_Connection=True.

If you are using Sql Authentication (as opposed to Windows Authentication) to connect to the Sql server, I believe you need to set Trusted_Connection=False.

This should allow your connection string credentials to be used (rather than the machine name/Windows service account/etc.).

Answer 2

Change the Run As setting on the service to run as your own personal account. This will provide the same security environment as your console test. This will allow the service permission to log into SQL Server.

Then change the service to "Local System" or similar and it will fail - you need to then grant "Local System" rights to access the database.

Also - an empty Catch block is the reason you are posting to SO - if you coded the simplest of error handler and loggers you would get your answer.

Answer 3

In your SQL Server, allow NT AUTHORITY/SYSTEM to server Role as sysadmin.

Follow the screenshots-