window service accessing SQL-Server 2005 -

Posted on 2011-04-20
Last Modified: 2012-06-27
I have a window services which watches a folder for a file copied to it, if one arrives it gives it a good name (rename) and is supposed to convert its contents to a sql-server 2005 database.

The rest of the code works fine but trouble is when the application trys to login to sql-server. it reports the error " Login failed for user 'NT AUTHORITY/LOCAL SERVICE'". below is the function with the code.

I the testing invironment, every thing works fine, but the window service fails at that stage

Thank you
Private Sub exportData(ByVal dname As String)
        Dim dline As String
        Dim oread As IO.StreamReader
        Dim k As Double
        Dim fullRecord As Boolean

        db = New SqlConnection("Data Source=Tony;Initial Catalog=phc2012;Integrated Security=True")
        If autoConv = 1 Then
            oread = File.OpenText(dname)
            k = 0
            While oread.Peek <> -1
                dline = oread.ReadLine
                If (dline & "").Trim.Length = 0 Then Continue While
                tstr = "SELECT * FROM [dictionary] WHERE (((secType)='" & Mid(dline, recTypeStart, recTypeLen) & "'))" _
                         & "  or (((secname) is null)) ORDER BY secname DESC, fldstart"
                cmd = New SqlClient.SqlCommand
                cmd.CommandText = tstr
                cmd.Connection = db
                drd = cmd.ExecuteReader
                If drd.HasRows Then
                    sql1 = Nothing : sql2 = sql1
                    Do While drd.Read
                        fstart = CType(drd("fldstart"), Int32) ' start of the field data
                        flen = CType(drd("fldlen"), Int16) ' field length
                        ftype = CType(drd("fldtype"), Int16) ' field length
                        If sql1 = Nothing Then
                            sql1 = "INSERT INTO [" & drd("secName").ToString & "] ("
                            sql2 = "values ("
                            fullRecord = False  'record has only ids no need to save it
                        End If
                        Dim s As String = Mid(dline, fstart, flen).Trim
                        If s.Trim.Length > 0 And s.IndexOf("*") = -1 And s.Trim <> """" Then
                            sql1 = sql1 & "[" & drd("fldname").ToString & "],"
                            fullRecord = True
                            If ftype = 1 Or flen = 3 Then 'integer/float
                                If IsNumeric(s) Then
                                    sql2 = sql2 & s & ","
                                End If
                            ElseIf ftype = 2 Then 'string
                                sql2 = sql2 & "'" & s & "',"
                            End If
                        End If
                    tstr = Mid(sql1, 1, sql1.Length - 1) & ") " & Mid(sql2, 1, sql2.Length - 1) & ");"
                    cmd = New SqlClient.SqlCommand
                    cmd.Connection = db
                    cmd.CommandText = tstr
                    'close the data reader to free resources for the next record
                    If fullRecord Then cmd.ExecuteNonQuery() ' save only valid record
                End If

            End While
        End If
    End Sub

Open in new window

Question by:Anthony Matovu
    LVL 83

    Expert Comment

    If you are using Windows Authentication, you need to add the account under which the service is running(Local System etc) to the SQL Server. Or use SQL Server Authentication.
    LVL 21

    Expert Comment

    by:Alpesh Patel
    Please change you service login using "NT AUTHORITY/LOCAL SERVICE" or "NT AUTHORITY/NETWORK SERVICE"
    LVL 20

    Accepted Solution

    Just to add to the other guys: to change the account you need to go to Administrative Tools -> Services, find your service, double click it to open properties, go to the tab Log On, select the radio This account and enter the username and password of a user which has access to the SQL Server database. If you reinstall the service, you will have to repeat the steps. Another option is to configure the installer of the service to set these details automatically (they are available in the installer properties).

    An option that will always work is to change your connection string to use SQL Server security but then the SQL Server must be configured to support it as well.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now