How can I configure the connection string to connect to a database that is not on the local computer? The sql server database is configured to accept remote access.

1. Yes, there is the MSDN documentation, which assumes a certain amount of background skill.

http://support.microsoft.com/kb/914277

2. Yes, you will have to change connection strings.

3. Most likely, if you have never connected to a remote server programmatically.

Have youbeen able to connect to the remote SQL Server using a tool such as Management Studio, or Visual Studio?

This staticmethod is from the same class in thesame DLL as above.

Code Snippet

/// <summary>

/// Builds connection string to default SQL Express instance.

/// Requires SQL Express to be installed on host machine.

/// </summary>

/// <param name="databaseName">The name of the database.</param>

/// <returns>A null String.</returns>

public static string ConnectDefaultSqlExpress(string databaseName)

{

StringBuilder sb = new StringBuilder();

sb.Append(IntegratedSecurity);

sb.Append(semiColon);

sb.Append(PersistSecurity);

sb.Append(semiColon);

sb.Append(InitialCatalog);

sb.Append(databaseName);

sb.Append(semiColon);

sb.Append(DataSource);

sb.Append(sqlExpress);

return sb.ToString();

}

/// <summary>

/// Builds connection string to remote SQL Express.

/// Requires SQL Express to be installed on target machine.

/// </summary>

/// <param name="databaseName">The name of the database.</param>

/// <param name="serverName">The UNC local server name.</param>

/// <returns>A null String.</returns>

public static string ConnectLocalInstanceSQL(

string databaseName,

string serverName)

{

StringBuilder sb = new StringBuilder();

sb.Append(IntegratedSecurity);

sb.Append(semiColon);

sb.Append(PersistSecurity);

sb.Append(semiColon);

sb.Append(InitialCatalog);

sb.Append(databaseName);

sb.Append(semiColon);

sb.Append(DataSource);

sb.Append(serverName);

return sb.ToString();

Walter,

I think the safest route is to use a simple configuration policy/scheme for your application, you have lots of choices available, the most obvious perhaps is to use the Applications own config/settings file. (app.config)

Making the parts of your connection string your going to need to change, variables to your application and then build the connection string based on the values stored there. Rudedog2's example of building the actual string is a good one, all you need to dois substitute the actual Data Source, InitialCatalog, UserID and pwd as variables.

Your application can then provide a simple form/user interface to change these values as needed making it easier to deployand configureusing the application rather than have to manually edit the configuration.

Here is some info on the ConfigurationManager class you'll need so that you read/write your values

http://msdn2.microsoft.com/en-us/library/system.configuration.configurationmanager.aspx

Other alternatives include using the Registry to store these connection string variables

Both ideas above rely on the fact your application will provide the means to change the settings, using some kind of interface you create...e.g Tools>Options or Tools>Config.

Steve

Hi Jazzyice

Below is a good starting point:

"Data Source=127.0.0.1;Initial Catalog=dbname;User ID=username;pwd=password;"

depending on your configuration you might also want to append one of the following:

"Trusted_Connection=[True/False];"

"Integrated Security=SSPI;"

Steve

Are able to connect to the remote instance using a tool like SQL Management Studio? Yes?

Is this remotecomputer on a LAN or across the internet? Yes?

Try this then....

...with this...

Code Snippet

/// <summary>

/// Builds connection string to remote SQL Express.

/// Requires SQL Express to be installed on target machine.

/// </summary>

/// <param name="databaseName">The name of the database.</param>

/// <param name="serverName">The UNC server name.</param>

/// <returns>A SQL Connection string.</returns>

public static string ConnectRemoteInstanceSQL(

string databaseName,

string serverName)

{

StringBuilder sb = new StringBuilder();

sb.Append(IntegratedSecurity);

sb.Append(semiColon);

sb.Append(PersistSecurity);

sb.Append(semiColon);

sb.Append(InitialCatalog);

sb.Append(databaseName);

sb.Append(semiColon);

sb.Append(DataSource);

sb.Append(serverName);

return sb.ToString();

}

Keep in mind what Steve said. There may be more to do depending upon your configuration. You many need to configure the security settings on the server and grant priviliges to a specific database, and then its associated tables.

So you put the data source as the IP of the server where the db resides? What does trusted connection mean?

Walter

Are you able to connect to the database on the remote server using a tool like SQL Management Studio, or the Visual StudioIDE?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2164297&SiteID=1

That link describes to setup SQL Express for remote Windows Authentication, then SQL Authentication. I have found that the standard links in MSDN do not fully describe all of the steps and nuances of actuallymaking it work. The links are good but they assume that you have a certain amount of background, and know what the related tasks are that you might needto perform.

Rudedog

Is there a better source? I am looking to deploy but my database (SqlExpress)is local to my machine. I am looking to export my database from the local sql express a full blown Sql db on a server using SSIS. The db is small enough that I can just redo it in Management studio. But when I export it to a server will I have to change connection strings? When I publish the application will I have other connection issues?

Walter

1. Yes, there is the MSDN documentation, which assumes a certain amount of background skill.

http://support.microsoft.com/kb/914277

2. Yes, you will have to change connection strings.

3. Most likely, if you have never connected to a remote server programmatically.

Have youbeen able to connect to the remote SQL Server using a tool such as Management Studio, or Visual Studio?

This staticmethod is from the same class in thesame DLL as above.

Code Snippet

/// <summary>

/// Builds connection string to default SQL Express instance.

/// Requires SQL Express to be installed on host machine.

/// </summary>

/// <param name="databaseName">The name of the database.</param>

/// <returns>A null String.</returns>

public static string ConnectDefaultSqlExpress(string databaseName)

{

StringBuilder sb = new StringBuilder();

sb.Append(IntegratedSecurity);

sb.Append(semiColon);

sb.Append(PersistSecurity);

sb.Append(semiColon);

sb.Append(InitialCatalog);

sb.Append(databaseName);

sb.Append(semiColon);

sb.Append(DataSource);

sb.Append(sqlExpress);

return sb.ToString();

}

/// <summary>

/// Builds connection string to remote SQL Express.

/// Requires SQL Express to be installed on target machine.

/// </summary>

/// <param name="databaseName">The name of the database.</param>

/// <param name="serverName">The UNC local server name.</param>

/// <returns>A null String.</returns>

public static string ConnectLocalInstanceSQL(

string databaseName,

string serverName)

{

StringBuilder sb = new StringBuilder();

sb.Append(IntegratedSecurity);

sb.Append(semiColon);

sb.Append(PersistSecurity);

sb.Append(semiColon);

sb.Append(InitialCatalog);

sb.Append(databaseName);

sb.Append(semiColon);

sb.Append(DataSource);

sb.Append(serverName);

return sb.ToString();

Walter,

I think the safest route is to use a simple configuration policy/scheme for your application, you have lots of choices available, the most obvious perhaps is to use the Applications own config/settings file. (app.config)

Making the parts of your connection string your going to need to change, variables to your application and then build the connection string based on the values stored there. Rudedog2's example of building the actual string is a good one, all you need to dois substitute the actual Data Source, InitialCatalog, UserID and pwd as variables.

Your application can then provide a simple form/user interface to change these values as needed making it easier to deployand configureusing the application rather than have to manually edit the configuration.

Here is some info on the ConfigurationManager class you'll need so that you read/write your values

http://msdn2.microsoft.com/en-us/library/system.configuration.configurationmanager.aspx

Other alternatives include using the Registry to store these connection string variables

Both ideas above rely on the fact your application will provide the means to change the settings, using some kind of interface you create...e.g Tools>Options or Tools>Config.

Steve

This is my first time. But yesterday after reading this I found the connect to remote server button in visual studio. I pressed it and it brought up an IP address of server field. I put in the IP address but was not able to connect. I am going to have to configure the server to accept remote access. I will let you know in a couple of days.

Thanks,

Walter

Hmmph! I just re-tested the method ConnectRemoteInstanceSQL() with theNorthWind database. You will need to change one line and add one variable.

Code Snippet

///

/// @"Data Source="

///

public const string Data_Source = @"Data Source=";

see the highlight below.

Code Snippet

public static string ConnectRemoteInstanceSQL(

string databaseName,

string serverName)

{

StringBuilder sb = new StringBuilder();

sb.Append(IntegratedSecurity);

sb.Append(semiColon);

sb.Append(PersistSecurity);

sb.Append(semiColon);

sb.Append(InitialCatalog);

sb.Append(databaseName);

sb.Append(semiColon);

sb.Append(Data_Source);

sb.Append(serverName);

return sb.ToString();

}