1

I have a console application that also runs as a windows service (see: .NET console application as Windows service)

It runs a stored proc that works on my local db, however over the network it only works as a console app, the service gives "SqlException (0x80131904):Cannot find the object or you don't have permission".

The user account has db owner permissions.

The very first thing the SP does is disable a trigger on a table, that it now says either does not exist or I don't have permission to access.

I am new to windows services, is there something I am missing? Do they work differently in this regard?

Its a sqlserver 2012 instance on another devs machine over the local network.

Connection string:

 <add name="XYZ_ConnectionString" connectionString="Data Source=PCNAME\sql2012;Initial Catalog=DB_Name;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>

EDIT: I have noticed that an earlier part of the service that does a select on a table from the DB is working correctly, which suggests the connection is working?

Thanks

Ed Norman
  • 1,108
  • 1
  • 9
  • 16
  • How are you connecting to your database? Please show your connection string (with sensitive data redacted if necessary). – spender Oct 09 '18 at 14:16
  • 5
    Remember that a service runs under its own account so if your using Integrated Authentication then the account needs to be configured within SQL Server. – Alex K. Oct 09 '18 at 14:18
  • Assuming the service itself is actually set to run as a domain account with the correct permissions? Also check the accounts default schema if you're using something other than dbo. – Apep Oct 09 '18 at 14:20
  • @AlexK. Yes it is set to run using a domain account that has been set up on the SQL server – Ed Norman Oct 09 '18 at 14:24
  • Is it the same account as the working console app uses? If not make sure its default database is setup correctly. A Profiler Trace should show more details about the error. – Alex K. Oct 09 '18 at 14:32
  • @AlexK. I have noticed that an earlier part of the service that does a select on a table from the DB is working correctly, which suggests the connection is working? – Ed Norman Oct 09 '18 at 14:38

2 Answers2

1

You're using Windows Authentication (the part of your connection string that reads Integrated Security=SSPI;) so when you run it as a console app it is using your logged in Windows ID to authenticate.

SQL Servers can be configured to use Windows Authentication or "Mixed Mode", which supports a textual username and password (details). Note that the default is to only use Windows Authentication.

If you are able to add Mixed Mode auth to the SQL Server (or it's already enabled) then get the database admin to create a new user and replace the Integrated Security=SSPI; part of your connection string with something like this:

;User ID=*****;Password=*****;

If you are not able to use Mixed Mode auth at the SQL Server (some DBAs are reticent to do this) then you need to configure your Windows Service to start with a Windows Domain account - note that it must be a Domain account, rather than a local Windows account on your PC, because the SQL Server needs to get to a domain controller to authenticate the credentials.

Info on configuring the service's credentials can be found here, but it's little more complex than opening the service in the service manager window, going to the "Logon" tab, and selecting the account that the service should run as.

Scott Leckie
  • 144
  • 9
1

If the select query is running correctly that seems to be a permission issue on SQL Server on the user excuting the operations.

Given that you are removing a trigger that does requires a different set of permissions that the ones needed to execute a select statement. I would suggest that you make sure the running user is getting proper access to the services, I would use SQL Server Profiler to log all the query and events to the database right after I run the application. In there I would see the queries that are failing and have a better picture of the root of the error.

Carlos Rivera
  • 166
  • 4