what is the difference between using data source = and using server = in connection string?

_{Thursday, November 15, 2012 6:38 PM}

Not able to find any specifics on this, but would greatly appreciate anyone weighing in:

Some suggest using connection string such as the below for SQL Express (note the data source= ):

data source=SQL_SERVER_NAME;initial catalog=mydb;User Id=LOGINNAME;

And others suggestion using connection string such as the below for SQL Express (note the server= ):

Server=COMPUTERNAME\SQLExpress;initial catalog=AyaNova;User Id=sa;

The questions are:

Is there a preference whether to use data source= or whether to use server= if using SQL Express (2008 or higher)?

And why?

And is there a preference whether to use data source= or whether to use server= if using SQL Server (2008 or higher)?

And why?

_{Sunday, November 18, 2012 11:22 AM ✅Answered | 1 vote}

Hello,

I think that there is no difference between Server and Data Source as they represent the same thing for SQL Server : the full name of the SQL Server instance with the syntax "MyComputerName\MyShortInstanceName" , potentially including the port used by the SQL Server instance to communicate.

Since 2007, i prefer to use a XXXConnectionStringBuilder ( XXX = Sql or ODBC or OLEDB ) for several reasons :

- the connection string is more secure ( injection problems are lowered )

- i can use the intellisense for the properties of XXXConnectionStringBuilder class , so i have not to ask me : is it datasource or data source ?

- when you have filled the properties of XXXConnectionStringBuilder, you have only to call XXXConnectionStringBuilder.ToString() method. If your connection string is invalid, you will receive a clear explanation message and it is avoiding to have a failed connection because you have provided a bad connection name/value for your connection string

It is a simple, clear, clean way of coding. For more explanations :

http://msdn.microsoft.com/en-us/library/d7z89tex(v=vs.100).aspx

http://msdn.microsoft.com/en-us/library/dce36088(v=vs.100).aspx

In the last link , you will find

"The SqlConnectionStringBuilder performs checks for valid key/value pairs. Therefore, you cannot use this class to create invalid connection strings; trying to add invalid pairs will throw an exception. The class maintains a fixed collection of synonyms and can translate from a synonym to the corresponding well-known key name"

Unluckily , the XXXConnectionStringBuilder classes seem to be ignored too often by people ( maybe because , it seems to be easier and quicker to provide a full connection string ( sometimes stored in the App.Config or Web.Config files without no encryption and easily to modify by anybody : a stupid way to conceive security )

Don't hesitate to post again for more explainations or help

Have a nice day

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

_{Friday, November 16, 2012 3:42 PM}

below is the correct connection string, you can use. Are you facing any issue?

data source=SQL_SERVER_NAME;initial catalog=mydb;User Id=LOGINNAME;

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click

Mark as Answer and

Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.