0

Running SQL Server 2008 R2 and ASP.NET MVC 2 web application on the Virtual Server of big german hosting-provider.

I have a problem to connect to the SQL Server.

On localhost this connection string works fine: 

 @"Data Source=LOCAL-HP;Initial Catalog=OnlineVertrag;Integrated Security=True";

I'm using a similar connection string on the server: 

@"Data Source=MYSERVER\SQLEXPRESS;Initial Catalog=OnlineVertrag;Integrated Security=True";

but nothing happens over there.

Here is my project which I am trying to connect: http://www.hotcont.eu/OnlineVertrag/Home

Improve this question
4
  • 1
    Can you confirm the port number for that instance? Commented Oct 24, 2012 at 0:58
  • 1
    In the connection string on the server you connect to SQLEXPRESS but in the local you don't. Is the database really on a SQLEXPRESS instance or on an own sql instance? For SQLEXPRESS try: Data Source=.\SQLEXPRESS Commented Oct 24, 2012 at 7:40
  • 1
    on the server SQL SERVER 2008 R2 has following data: Username:MYSERVER\Administrator, Connection:MYSERVER\SQLEXPRESS. i tried as you said with .\SQLSERVER, it doesnt work. very annoying. Commented Oct 24, 2012 at 9:26
  • 1
    to confirm if you got the right server and credential settings can you open that SQL Server from SSMS? By connecting to Server Name: MYSERVER\SQLEXPRESS; Authentication : SQL Server Authentication; Login : MYSERVER\Administrator; Password : the one they provide you Commented Oct 31, 2012 at 22:53

2 Answers 2

Reset to default
4
+50

Based on your comments, your SQL instance on the server has a username/password combination. You cannot use the integrated security connection for a SQL Server connection requiring SQL authentication.

Using the login information provided to you by your host, you should update your connection string to the following:

Server=MYSERVER\SQLEXPRESS;Database=OnlineVertrag;User Id=myUsername;
Password=myPassword;

Taken from ConnectionStrings.com

EDIT

Ok - I think I know what you are dealing with now. You have a virutal machine hosted on a large hosting provider -> meaning that you have control over the machine itself (aka Remote Desktop Management or something?)

The SQL connection string that I provided is for use with SQL server accounts -> meaning those that you actually create within SQL server itself. The SQL connection string that you provided uses the current logged in user's user account information from Windows to connect to SQL.

So here is the disconnection between localhost and the virtual server. When you are running on localhost, I am going to assume that you are using the built in web server to Visual Studio or some equivalent. Most often, during debugging, the web application is running under the Logged in user of the machine - aka: you. You have permission to your own SQL database, thus no issue. BUT...when you deploy your web application to an IIS instance, the web application is no longer running as the logged in user, but rather the identity of the application pool that is your app is a member of. Typically this is something like NETWORKSERVICE.

You have three options available to you

  1. Enable and use SQL user accounts for connection from your web application and your SQL server. If you choose to go this route, you will need to use the connection string I provided above.

  2. Login to your SQL server and add the identity of your application pool to the Allowed Users of SQL server and your database.

  3. Change your application pool's identity to an actual user account on the server (BAD IDEA)

Most web applications go with the first option as it allows you do a few things such as create a distinct SQL user for each application that you host and as well as you can explicitly define permissions for the SQL user to each database that it may need access to (for instance, do not allow the SQL user to DROP tables).

EDIT 2

The way you are trying to connect sounds like it should be using the Shared Memory Protocol, but it might be trying to connect over TCP/IP. I forgot this earlier, but most installs of SQL are not setup to listen on the TCP/IP interface on first install. To check your configuration, click the start button (or orb or whatever Microsoft calls that now) -> All Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager. This will open a new window with some options on the left hand side. Click the SQL Server Network Configuration. Ensure that TCP/IP and Shared Memory is set to enabled. If a 64 bit install, you should probably do this for both the SQL Server Network Configuration (32 bit) and the SQL Server Network Configuration

http://msdn.microsoft.com/en-us/library/ms191294.aspx

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

is it username and password of windows authentication?
When you run on your localhost (inside of the VS web server), the web application process is running as the logged in user of the computer (aka you) -> which is why you do not have to specify a username in the SQL connection string. On your web server (assuming its a virtual machine you can manage) -> your web application is running under something like MYDOMAIN\NetworkService. This 'user' does not have access to the SQL server instance.
So you can either a) create a SQL user account of your web application and use the connection string provided or b) Add the windows account of your web application pool to the allowed users of the SQL server and the SQL database itself. I probably should have just updated my answer...
:( trying everything as you said. i will cry. nothing works. it so sad. second day working on it.
One other thing you can/should check - Edit in my answer
0

Try this 

Data Source=MYSERVER\SQLEXPRESS;Initial Catalog=OnlineVertrag;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False

I hope this works for you.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.