Unable to connect to SQL Server 2008 in .Net

Question

I am working on a C# web service.

I have deployed a web service which is working perfectly on my local system but when I try to run the same service on my virtual dedicated server it is returning an error.

This is my connection string

Data Source = <serverinstance>\\SQLEXPRESS; Initial Catalog = DomainTable; User ID= <serverinstance>\\admin; Password = <Windows_Login_Password>

Before this I was using this connection string

Data Source=<serverinstance>\\SQLEXPRESS; Initial Catalog=DomainTable; Integrated Security=SSPI";

None of them is working for me. please help its really really important

Answer 1

Try just;

"data source=.\\SQLEXPRESS;Integrated Security=SSPI;initial catalog=DomainTable"

I believe the user name / password are generally only required for remote connections, and you must set them up for the specified DB. The above connection string should get you going on the running instance of sqlexpress on the local machine if you have not made login credentials required.

Edit

If you are using IIS to host, here is a chunk from a recent article I did for deploying and setting up App pools to work with SQL Express 2008. Even if you are not using IIS, you will still need to do something like this to get your hosting environment set up correctly with SQL Server.

Step 1 Create a DB Logon for the IIS USER

In the security section in MS SQL SERVER, need to right click users and your IIS APPPOOL(IIS APPPOOL\apppoolname, created in Section one, Step 2).Check out dp.'s answer here for more step by steps - Add IIS 7 AppPool Identities as SQL Server Logons

Step 2 Setting Permissions for the IIS USER on the db

Right click on the database name, and click properties. In permissions section add your APPPOOL user that you just created, be sure to give him the Execute \Select permissions needed .

Note: Website1 needs Execute, Select, Delete, Insert permissions as well. Website2 is also using this app pool which it shouldnt be because we dont not need to alow the user to have that kind of functionality from within the trimdynamics application. We could seperate the app pools so that our trim dynamics IIS USRS is only getting Execute and Select permissions.

If this does not help, it is hard to tell whats really going on without concrete knowledge of your hosting structure, as there are lot's of gotch-ya's when deploying from development to production environment :(

Answer 2

Data Source=serverinstance\SQLEXPRESS;
-----------------------------------------^ only one slash here

Also make sure that the remote server can see whatever you've referenced as <serverinstance>. You may have this as localhost but your web server does not consider your machine to be localhost. It may also not be able to reach it by name, particularly if it is in a different domain.

If you are trying to use Windows Authentication, there is no way on earth you should be using:

User ID = serverinstance\admin; Password = Windows_Login_Password

Wow, never hard-code a Windows password anywhere. If you want to use SQL authentication, then set up . Otherwise you should be using this instead of User ID / Password:

Persist Security Info = true;

And ensuring that the IIS user has been granted access to your SQL Server.