windows service and SQL related problem
-
I have created a service in C#. The service loads a COM dll (it uses OLE DB classes) on startup which handles the database connectivity part (SQL server). When I startup the service it works fine for a local database but when the database is remote the Open fails with an error. The exception that is written into the Event Log is as follows: "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied." "System.Runtime.InteropServices.COMException (0x80004005): [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. To zero-in on the problem I ported the Database related code into a separate exe and found that it works fine. SO this problem seems specific to a Service. I am running the service logged is as a System Administrator and given full access to it at the Remote server. Has anybody faced this problem before. Pls help. Coding is injurious to health!!
-
I have created a service in C#. The service loads a COM dll (it uses OLE DB classes) on startup which handles the database connectivity part (SQL server). When I startup the service it works fine for a local database but when the database is remote the Open fails with an error. The exception that is written into the Event Log is as follows: "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied." "System.Runtime.InteropServices.COMException (0x80004005): [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. To zero-in on the problem I ported the Database related code into a separate exe and found that it works fine. SO this problem seems specific to a Service. I am running the service logged is as a System Administrator and given full access to it at the Remote server. Has anybody faced this problem before. Pls help. Coding is injurious to health!!
1. If you're connecting only to SQL Server and not any other DB systems, use the
System.Data.SqlClientinstead of OLE DB. These are classes written specifically for SQL Server with a lot of optimizations and additional features over OLE DB. 2. The problem seems to be security. When you put it in a Windows app, it runs under your user credentials (be default). If you are using SSPI to connect to the SQL Server with a trusted connection, your credentials are authenticated and authorized by SQL Server, so you're app can access it. Typically, most Windows Services use the LocalSystem account. If your connection string is using SSPI and the LocalSystem (SYSTEM) account is NOT added to SQL Server as a viable user that can access the system, you'll get Access Denied. You should either use a fixed User ID and Password in the connection string, as well as Trusted Connection. Or, continue to use Integrated Security=SSPI and add the SYSTEM account to SQL Server for that database and grant it the rights necessary to run (I wouldn't recommend making it a dbo, though).-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----
-
1. If you're connecting only to SQL Server and not any other DB systems, use the
System.Data.SqlClientinstead of OLE DB. These are classes written specifically for SQL Server with a lot of optimizations and additional features over OLE DB. 2. The problem seems to be security. When you put it in a Windows app, it runs under your user credentials (be default). If you are using SSPI to connect to the SQL Server with a trusted connection, your credentials are authenticated and authorized by SQL Server, so you're app can access it. Typically, most Windows Services use the LocalSystem account. If your connection string is using SSPI and the LocalSystem (SYSTEM) account is NOT added to SQL Server as a viable user that can access the system, you'll get Access Denied. You should either use a fixed User ID and Password in the connection string, as well as Trusted Connection. Or, continue to use Integrated Security=SSPI and add the SYSTEM account to SQL Server for that database and grant it the rights necessary to run (I wouldn't recommend making it a dbo, though).-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----
Good advice Heath, One more thing to add, is that if your service and SQL Server are on different boxes then you can't use or add System to an authorized account on SQL Server. Local System, uses a null user and null password to make autorization attempts against remote resources, unless you have null session shares turned on (NEVER DO THIS) it won't work. You're best be is to create a domain account for your service, configure your service to run under this account and add this account to your sql server as a login and a user in your database. If you do not have a domain, then as long as the same account exists on both boxes and has the same password you can still connect.
-
1. If you're connecting only to SQL Server and not any other DB systems, use the
System.Data.SqlClientinstead of OLE DB. These are classes written specifically for SQL Server with a lot of optimizations and additional features over OLE DB. 2. The problem seems to be security. When you put it in a Windows app, it runs under your user credentials (be default). If you are using SSPI to connect to the SQL Server with a trusted connection, your credentials are authenticated and authorized by SQL Server, so you're app can access it. Typically, most Windows Services use the LocalSystem account. If your connection string is using SSPI and the LocalSystem (SYSTEM) account is NOT added to SQL Server as a viable user that can access the system, you'll get Access Denied. You should either use a fixed User ID and Password in the connection string, as well as Trusted Connection. Or, continue to use Integrated Security=SSPI and add the SYSTEM account to SQL Server for that database and grant it the rights necessary to run (I wouldn't recommend making it a dbo, though).-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----
Did you actually read his question? Heath Stewart wrote: 1. If you're connecting only to SQL Server and not any other DB systems, use the System.Data.SqlClient instead of OLE DB. These are classes written specifically for SQL Server with a lot of optimizations and additional features over OLE DB. His data access is being done from a COM dll, not a .NET component. You are answering the wrong question. Heath Stewart wrote: Typically, most Windows Services use the LocalSystem account. If your connection string is using SSPI and the LocalSystem (SYSTEM) account is NOT added to SQL Server as a viable user that can access the system, you'll get Access Denied. He is very clear about the fact that he is _not_ running the service under LocalSystem. Heath Stewart wrote: Typically, most Windows Services use the LocalSystem account. If your connection string is using SSPI and the LocalSystem (SYSTEM) account is NOT added to SQL Server as a viable user that can access the system, you'll get Access Denied. He's also very clear that the database is on a remote machine, in which case granting LocalSystem access won't help at all because they are two unrelated LocalSystem accounts. Heath Stewart wrote: You should either use a fixed User ID and Password in the connection string, as well as Trusted Connection *sigh* I'm not even going to go there. Fortunately Terry's answer already homes in on what is likely the real problem. -Blake