lee_K
asked on
C# Console Application cannot see SQL Server when running as a Scheduled Task?
Hi all, I have developed a simple C# Console application. In the connection string I am using a named SQL Server Login, so not Windows Auth and the application works fine and I can connect to the database.
I set this up to run as a scheduled task, and it runs when the Task is set up to use my Username/Password with no problems.
I created a new user specifically to run this (good practise to not have it running as me obviously), added them to the Administrators group of the machine, and configured the Task to use this new user.
When the task runs using the new user, the code fails with the following error on each of the attempts to access the database.
'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)'
Which is your standard SQL Server Access Denied error. I also tried configuring the task to run as the NT AUTHORITY\SYSTEM user and get the same problem. I used SQL Profiler to ensure the user coming through was the one named in the connection string and that is fine.
To double check it is related to the user, I logged into the machine as the new user and tried running from the command line and I get the same error.
Any ideas? Thanks in advance.
I set this up to run as a scheduled task, and it runs when the Task is set up to use my Username/Password with no problems.
I created a new user specifically to run this (good practise to not have it running as me obviously), added them to the Administrators group of the machine, and configured the Task to use this new user.
When the task runs using the new user, the code fails with the following error on each of the attempts to access the database.
'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)'
Which is your standard SQL Server Access Denied error. I also tried configuring the task to run as the NT AUTHORITY\SYSTEM user and get the same problem. I used SQL Profiler to ensure the user coming through was the one named in the connection string and that is fine.
To double check it is related to the user, I logged into the machine as the new user and tried running from the command line and I get the same error.
Any ideas? Thanks in advance.
If you are using ODBC drivers to connect to SQL server, you should configure SQL client in ODBC sources under this new user not to use Named Pipes but TCP/IP.
You should use SqlConnection, but before it, you must try logging in from sql server management studio using that users username, from which you are logging in
ASKER
brousil: Hi there, I am pretty sure I am using the .Net drivers with System.Data.SqlClient
nkhelashvili: If I understand correctly, you are suggesting logging in using new Windows user set up to run task (called TaskRunner) and starting SQL Management Studio and logging in as SQL user in connection string. That might be tricky as where I am deploying it has no SQL Management studio. But I see your logic.
nkhelashvili: If I understand correctly, you are suggesting logging in using new Windows user set up to run task (called TaskRunner) and starting SQL Management Studio and logging in as SQL user in connection string. That might be tricky as where I am deploying it has no SQL Management studio. But I see your logic.
Which version are you using, have you sql server express?
You must change it from Sql Server 2005 Surface Area Configuration:
Remote connectons --> local and remote connections --> using both tcp/ip and named pipes.
Try this
Remote connectons --> local and remote connections --> using both tcp/ip and named pipes.
Try this
One more thing: if you are trying to log in from command line, ensure you are using following format
SQLCMD -S machinename\sqlinstancename -U yourusername -P yourpassword
If your connections string in your app is using Trusted (Integrated) Security then in order to test it I would change the above SQLCmd to:
SQLCMD -S machinename\sqlinstancenam
SQLCMD -S machinename\sqlinstancenam
You can also try sql server client utility and enable TCP/IP.
c:\windows\system32\clicon
c:\windows\system32\clicon
ASKER
Thanks all for the responses. I don't think it should be named pipes or TCP/IP config as one user works with no problems, but I am willing to try. I am arranging to speak to the people who administer the DB machine to check this out. Wil get back. I am on 2005 SP2.
Thanks again
Thanks again
ASKER
Hi all, I have checked using Surface Area confiuration tool and it is set up to use TC/IP and Named pipes. I tried that client config thing Brousil but with no luck. I am going to speak to the server admin person later today and will report back any more information.
ASKER
I just wanted to update people on this, I think the problem is related to Users and Windows accounts, but can only post a solution when I have found out myself.....watch this space
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.