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

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

Follow the screenshots-

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.