connection string on virtual dedicated server not working

Question

I'm moving from a shared hosting to Virtual Dedicated Server (both at GoDaddy). Suprisingly, everything went fine up until now. However, now my web application seems unable to connect to the database (which I created on the server).

I tried several options, including giving it the server name etc. for example:

<add name="dbname" connectionString="Data Source=servername\SQLEXPRESS2008;Initial Catalog=theDB;User Id=servername\usernameAcceptedByMangmentStudio;Password=mypwd; Trusted_Connection=False;"/>

also:

<add name="dbname" connectionString="Data Source=.\SQLEXPRESS2008;Initial Catalog=theDB;User Id=usernameAcceptedByMangmentStudio;Password=mypwd; Trusted_Connection=False;"/>

also:

<add name="dbname" connectionString="Data Source=servername\SQLEXPRESS2008;Initial Catalog=theDB; Integrated Security=True;>

(this is how it works on my local machine).

nothing seems to work. any ideas? thanks in advance.

Answer 1

If your DB has UserName and Password(SQL Server Authentication) just simply use this:

<add name="dbname" connectionString="Data Source=yourServerName;Initial Catalog=YourDB;User ID=YouruserId;Password=YourPassWord" providerName="System.Data.SqlClient"/>

FYI: Make sure your database Server Authentication is SQL Server and WindowsAuthentication mode

You can check also : Connection strings for SQL Server 2008 for more info.

Regards

Answer 2

It seems you don't know whether you have a named instance or a default instance. Or even the difference between the two.

First I'll explain the quickest way to determine which you have, then I'll explain the differences.

You need to connect to your server (I assume you can remote desktop or something similar), then open Control Panel > Administrative Tools > Services. In that list there should be an item called SQL Server, and it will either look like this:

SQL Server (MSSQLSERVER)

Or this:

SQL Server (some_name)

The former is a default instance. If this is what you see, then the connection string should be:

"Data Source=localhost;..."

The latter is a named instance. If this is what you see, your connection string should be:

"Data Source=localhost\some_name;..."

If you see both, then you need to tell us how you initially connected to this instance in order to create the database (and you need to decide which one you want to use, since you probably don't need both).

If that doesn't help, you need to be more specific than "doesn't work." Are you unable to connect to the server, or the database? What is the actual error message? When you created the database, did you use the User Instances / AttachDbFileName features? Can you show a screen shot of the connection dialog in Management Studio that is successful? Is your app on the server or on your local machine? Is it possible you should be specifying the IP address instead of various local references? Have you contacted GoDaddy support about your problem? They are far better equipped to help you than a programming Q&A site, since they can connect to your server in 10 seconds and tell you exactly how you should be referencing the SQL Server instance.

As for the differences, named instances are typically used when you need to install multiple individual instances of SQL Server. This is not usually a requirement on a production machine; much more common on development machines where you must test multiple versions, simulate different environments, or temporarily during a migration or side-by-side upgrade. A default instance listens out of the box on port 1433. Named instances run on other ports and usually use the SQL Browser service for remote machines to identify how exactly to connect to them.