Remote access to SQL Server on indra

Connecting to SQL Server in indra

When connecting to SQL Server from either Visual Studio's Server Explorer or SQL Server Management Studio, use indra.stockton.edu,9999 as the server name, use SQL Server Authentication and your login credentials on the remote server. Once a connection is established, you can to all the operations on the remote server as you would on your local development server.

Transfering table definitions, data. stored procedures, etc. from an App_Data database file

1. In Visual Studio, go to the Server Explorer and right click on the local database to copy. Choose Publish to Provider... to launch the database publishing wizard.

2. Select the mdf file to be published.

3. Choose the Script to file option and select a location for the SQL script file which will generate your database tables on a remote server.




At  the publishing options step, use SQL Server 2008 as the target database if this option is available. If the database tables do not exist on the remote server yet, publish Schema and data. You can use Data only if you are just copying data to existing tables. If you are using SQL Server Management Studio, more options will be present in this window.



Click through the remaining wizard steps to generate the script.

3. The script file created in the previous step needs to be executed on the remote server. Open the SQL script file in either Visual Studio or or SQL Server Management Studio (in Management Studio use File with a new Connection.... ). Execute the script (in Visual Studio you will need to connect to the server).

Yet another option is to open a cmd (DOS) window and switch to the directory that contains your script. Enter the following command (substitute your account credentials in place of the user name, password, and database name and use your script file name in place of script.sql:

sqlcmd -U myid -P mypaswd -i script.sql -S indra.stockton.edu,9999 -d myid

 

Connecting to the database from an application

You can connect to the database on the remote server from your application running from Visual Studio by using a connection string in Web.config similar to the following (replacing ?'s with the proper numbers):

connectionString="data source=indra.stockton.edu,9999;user=myid;password=mypaswd;initial catalog=myid"

When loading your application on the server, use a connection string in Web.config similar (note the change in the data source field needed when running the app on the remote server):

connectionString="data source=.\SQLSERVER2005;user=myid;password=mypaswd;initial catalog=myid"