milicica

How c# app connect to sql server on another pc in local network
Is connection string same when server on local pc
Is there diversity if server is sqlexpress

Thanks for any help.


Re: .NET Framework Data Access and Storage How c# app connect to sql server on another pc in local network?

decyclone

Hi Milicica,

Well, Here is the simple way to connect to any SQL server in the LAN :

Just create a .udl file say test.udl.
Open it and it will show you tab view with 4 tabs.
Go to Provider tab and select the provider you want to connect to.
For SQL server, select Microsoft OLE DB Provider for SQL Server.
Go to Connection tab and see the values in the server name combobox.
It will be filled with the available servers on the LAN.
Select the one you want to connect to.
Enter username and password.
If there is a password, check allow saving password.
Select the database on the server.
And Test the connection.
Click OK and the dialog will close.
Now Open that file in the notepad.
And you will have the connection string you can use to connect to the specified database.

This way you can create the connection string without the help of any IDE or other applications.


I created the connection string while writing this :

Provider=SQLOLEDB.1;Persist Security Info=False;User ID=[Username];Password=[Password];Initial Catalog=[Database Name];Data Source=[Computer Name]\[Server Instance Name]

I think there is no change in the connection string if the Server is local or on LAN besides the Computer Name and Server Instance Name part.

And I dont think there is any diversity if the Server is SQL Express.

Just Replace the plcaeholders with according details.

I hope it helps.

Jay Joshi,
decyclone@gmail.com





Re: .NET Framework Data Access and Storage How c# app connect to sql server on another pc in local network?

OmegaMan

milicica wrote:
How c# app connect to sql server on another pc in local network
Is connection string same when server on local pc
Is there diversity if server is sqlexpress

Thanks for any help.


Answers:

1) The same way the operating system communicates over the network tcp/ip.

2) The connection string format will be the same, but just like anything local vs remote, the server name or ID (whether local or remote) has to be resolved, either by name or an IP. That doesn't mean one couldn't reference a local box by its own IP or naming conventions. It depends on how nice DNS is to you whether one has to decorate a database server name with domains and other items.

3) What do you mean by diversity The express version is just a trimmed down version of the 2005 database. In most repects it is the same as its more robust cousin.





Re: .NET Framework Data Access and Storage How c# app connect to sql server on another pc in local network?

utkuozan

Well it certainly will be a bit different. I think a comparison on two scenarios would be good to comprehend.

Calling an SQL connection from a local machine:

connectionString="Data Source=local;Database=your_database;UID=your_username;PWD=your_password"

Calling an SQL connection from a remote machine:

connectionString="Data Source=remote_machine;UID=your_username;PWD=your_password;Initial Catalog=your_database"

The main difference is the connection type the system uses. While connecting to a remote machine you need SQL Server Authentication and while connecting to a local server Windows authentication would be enough. So you might even consider omitting the username and password part while connecting to a local machine.






Re: .NET Framework Data Access and Storage How c# app connect to sql server on another pc in local network?

OmegaMan

utkuozan wrote:

While connecting to a remote machine you need SQL Server Authentication and while connecting to a local server Windows authentication would be enough.



That is an excellent observation. Due to the complexities of impersonation, the remote connections are different.....that has been the case for my database connections.





Re: .NET Framework Data Access and Storage How c# app connect to sql server on another pc in local network?

SerenityPCCS

However, if the connections are in a Domain environment, then Windows Authentication works even remotely assuming the servers are set up properly. This is exactly how I use SQL Server. The server sits on one machine, and the client runs on a PC. It uses Windows Authentication, but you need to set Integrated Security = true.



Re: .NET Framework Data Access and Storage How c# app connect to sql server on another pc in local network?

milicica


Thanks for your answers.

I use below conection string, but there's appear error in remote connection. On same pc connection is OK.

mConnection = new SqlConnection(@"data source=servername\sqlexpress;" +
"database=databasename;" +
"UID=user;PWD=pass;");

Error mesage is:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)


Server Authentication is SQL Server and Windows Authentication mode.
Allow remote connections to this server is set.

What may cause this mistake





Re: .NET Framework Data Access and Storage How c# app connect to sql server on another pc in local network?

Kusala

Did you enable the TCP/IP (also Named Pipes) protocol in Remote SQL Express (normally those are disabled). Sometimes this be the cause for above mentioned problem

To enable these options, go to:

Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager -> SQL Server 2005 Network Configuration -> Protocols for SQLEXPRESS

[You can do the same using SQL Server Surface Area Configuration too]

Check following link also:

http://www.sql-server-performance.com/faq/sqlviewfaq.aspx topicid=1&faqid=143

Kusala





Re: .NET Framework Data Access and Storage How c# app connect to sql server on another pc in local network?

milicica


Thanks a lot Kusala, there was a problem.




Re: .NET Framework Data Access and Storage How c# app connect to sql server on another pc in local network?

Mehmet Altan Toksöz

-First try a connection like this;
ConnectionString = "Data Source=ComputerName\\SQLEXPRESS ;Initial Catalog=yourDatabaseName;Persist Security Info=True;User ID=yourID;Password=yourPassword;TrustServerCertificate=False;User Instance=False";

-Second make sure that the server's FireWall is closed or allows sql connections.

-Third make sure that SQLEXPRESS and SQL SERVER BROWSER is in running state.

-Fourth make sure that TCP/IP option in sql sever configuraton part is ENABLED. and also
right click on this part and you will see the ip addresses part and just enable the IP1

- Lastly change the registry "HKLM\\SOFTWARE\\MICROSOFT\\Microsoft SQL Express\\
MSSQL.1\\MSSQLSEVER\\LoginMode=2" because login Mode 1 is windows authentication.

So in the client part application connection string is normally read only. It look likes this;
public string YourConnectionString {
get {
return ((string)(this["YourDBConnectionString"]));
}
}

To Enable this, just add a set part;
public string YourConnectionString {
get {
return ((string)(this["YourDBConnectionString"]));
}
set
{
this["YourDBConnectionString"] = value;
}
}

i hope this helps you.
Mehmet Altan Toksoz