Connecting To SQL Server DB Using VS2008

Question

I am using VS2008, and usually I'll develop my sites from the App_Data folder and then just upload the whole site to the server.

Now I have a site and the SQL DB is running on the server already (Not in the App_Data folder , I use SQL Studio Mgement to connect to it and edit), so instead making the changes to the local DB and then again to the one on the server. Can I just connect live to the DB on the server using Visual Studio 2008? Then I can at least see the live data and make changes straight away if needed?

Any tutorials on how to set this up very appreciated

(I realise this is probably not an ideal situation, but its a personal project and only me working on it)

Answer 1

Why not access live from SQL management studio?

Answer 2

You can connect to your remote server by using an appropriate connection string.

This goes in your web.config file:

...
<configuration>
  ...
  <connectionStrings>
    <add name="MyConnectionString" connectionString="Data Source=SERVERNAME;Initial Catalog=MyDatabase;Integrated Security=True"
     providerName="System.Data.SqlClient" />
  </connectionStrings>
  ...
</configuration>
...

Then, from your code, you can retrieve the connection string from the web.config file:

string myConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

and use it connect to the database using the SqlConnection class:

using (SqlConnection connection = new SqlConnection())
{
    connection.ConnectionString = myConnectionString;
    connection.Open();
    // use the connection ...
}

There are several options you can specify in your connection string for specifying credentials and other properties. See the SqlConnection.ConnectionString documentation for a table of various properties you can set.

Just be careful: you're using a live database and you don't want to break things! A good idea would be to grab a copy of your live database and run it in a local instance of SQL Server (even SQL Server Express Edition).

Answer 3

You obviously want to to connect to a ASPNETDB that isn't located in the default directory of the web project, so...

I found a good method to move the location of the ASPNETDB database in Visual Studio 2008 to a different location on a machine wide level. You can override this setting by editing the webapp.config but I consider that more complicated and unnecessary in most cases.

By default, new web projects will place your application database in the App_Data folder and that allows you to use a file based SQL. I don't like having my applications core database off of the root of the publicly accessible site and so I prefer the application connect to the SQL server service in the usual way.

You can setup your dev project to reflect the live site you deploy to. To accomplish this just do the following for Visual Studio 2008:

A. Uninstall all previously existing Microsoft SQL server instances, including SQL 2005 and any lite or compact editions. This requires a system restart. Note: backup any databases you have by detaching them with SSMSE and then saving the mdf and ldf files somewhere safe. If you are running x64 Vista then you can delete the SQL Server directories in the x86 Program Files directory.

B. Reboot and install SQL Server 2008. After installation, enable "Named Pipes" and "TCP" listener protocols for the service. Also, enable the "sa" account and set the password for the "sa" account. Also, change SQL server instance to use "mixed mode authentication".

C. Edit the C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config so that you have this in the connectionstrings section:

ADD name="LocalSqlServer" providerName="System.Data.SqlClient" connectionString="data source=LOCAL\SQLEXPRESS;Integrated Security=SSPI;database=aspnetdb"

D. Find the aspnet_regsql.exe and run it to create the aspnetdb database. When it asks for the database name choose "default" and it creates a database called "aspnetdb".

E. Make the app useable via IIS without your debug environment by using non-integrated security. You need to "enable" the disabled "sa" account, make "sa" the owner of the aspnetdb database, and then make sure your SQL Server has "SQL Server Authentication Mode" enabled. Finally, add this section to your webapp.config file:

*ADD name="LocalSqlServer" providerName="System.Data.SqlClient" connectionString="Server=LOCAL\SQLEXPRESS;Database=aspnetdb;User Id=sa;Password=password;Trusted_Connection=False"*

Answer 4

There could be some issues with that kind of set-up depending on how the SQL Server security was set up. You can get into a double-hop network issue for authorization. If the SQL Server is only set up for Integrated Windows Authentication is where we ran into a bit of difficutly. The user would authenticate to the web server but since the SQL Server was on a different machine the credentials sent to it would be the web servers credentials and not the user there fore makeing any calls to the SQL Server fail because of security rights. Some settings would need to be adjusted in Active Directory to allow the web server to be authorized for delegation and Kerberos security will need to be set on your network as well. Your situation may be different but just something to look out for.

Answer 5

In VS2008 Menu, Click Tools -> Click "Connect to Database..." -> Enter the correct SQL credentials in the "Add Connection" dialog box -> View DB connection on Server Explorer pane.