• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

window service accessing SQL-Server 2005 - vb.net

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")
        db.Open()
        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
                    Loop
                    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
                    drd.Close()
                    If fullRecord Then cmd.ExecuteNonQuery() ' save only valid record
                End If

            End While
            oread.Close()
        End If
    End Sub

Open in new window

0
Anthony Matovu
Asked:
Anthony Matovu
1 Solution
 
CodeCruiserCommented:
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.
0
 
Alpesh PatelAssistant ConsultantCommented:
Please change you service login using "NT AUTHORITY/LOCAL SERVICE" or "NT AUTHORITY/NETWORK SERVICE"
0
 
TheAvengerCommented:
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.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now