1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Pirate
  • 545
  • 1
  • 6
  • 15
  • Is the database running on the same machine as IIS? – Andre Calil Jul 05 '12 at 19:14
  • 1
    Please include *actual* details, such as what you have for `` (is that a server name, localhost, an IP address, etc.) and what "returning an error" means - e.g. include the *actual error*. – Aaron Bertrand Jul 05 '12 at 19:16
  • yes sqlserver 2008 is installed on the same machine – Pirate Jul 05 '12 at 19:17
  • i am using a virtual dedicated server and the name of SQL server is S15994212\SQLEXPRESS – Pirate Jul 05 '12 at 19:18
  • 1
    be easier if we could see the error but presuming it's a login error, is the app pool user able to hit that sql instance? – Chris Simpson Jul 05 '12 at 19:19
  • i am able to connect to the SQLServer using sql server management tool using same credential and windows authentication – Pirate Jul 05 '12 at 19:20
  • And is the app on S15994212 or a different server? Is that server in the same domain? Can you ping that server name from the web server? Where is Management Studio located, on your machine, the web server, or the database server? What is the actual error message? Do you see how much information is still missing here? If it's really important why are you so reluctant to share the very information that will help us help you solve the problem? – Aaron Bertrand Jul 05 '12 at 19:22
  • S15994212 is on the same server, this server is on the same domain, i have not tried this, as my web server and database server are on the same machine therefore management studio is on the same machine, is there anything else do you need ??? – Pirate Jul 05 '12 at 19:28
  • @AaronBertrand this is the exact error message which we are getting "Cannot open database "DomainTable" requested by the login. The login failed. Login failed for user 'S15994212\IWAM_plesk(default)'." thanks for your patience :) – Pirate Jul 05 '12 at 19:36
  • Never, ever, **ever** hard-code a Windows login password anywhere. Ever. Did I mention ever? – Aaron Bertrand Jul 05 '12 at 19:40
  • I think i haven't done that as well.. – Pirate Jul 05 '12 at 19:46

2 Answers2

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
0

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 :(

Community
  • 1
  • 1
clamchoda
  • 4,411
  • 2
  • 36
  • 74
  • 1
    @Pirate DEFINE "not working" please. e.g. add the ***actual error message*** to your question! – Aaron Bertrand Jul 05 '12 at 19:27
  • @Pirate just noticed you deployed it on your development server. This causes a lot of headaches your first time through. You have to think of it like this - Now IIS / some services is hosting your website, not your local machine, things are a little different. In your development environment, everything works fine because your executing queries as the current user, which has privileges. When you host your application, for instance in IIS, you know have an IIS Pool rying to execute the queries on your db, which does not automatically have permissions. Are you receiving User permission erro – clamchoda Jul 05 '12 at 19:31
  • @ChrisBuckler this is the error message which we are getting "Cannot open database "DomainTable" requested by the login. The login failed. Login failed for user 'S15994212\IWAM_plesk(default)'." – Pirate Jul 05 '12 at 19:35