SQL SERVER – 2005 – Connection Strings For .NET

SQL Native Client ODBC Driver

Standard security

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;
Uid=myUsername;Pwd=myPassword;

Trusted Connection

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;
Trusted_Connection=yes;

Connecting to an SQL Server instance

Driver={SQL Native Client};Server=myServerName\theInstanceName;Database=myDataBase;
Trusted_Connection=yes;

SQL Native Client OLE DB Provider

Standard security

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;
Uid=myUsername;Pwd=myPassword;

Trusted connection

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;
Trusted_Connection=yes;

Connecting to an SQL Server instance

Provider=SQLNCLI;Server=myServerName\theInstanceName;Database=myDataBase;
Trusted_Connection=yes;

SqlConnection (.NET)

Standard Security

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Trusted Connection

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

Connecting to an SQL Server instance

Server=myServerName\theInstanceName;Database=myDataBase;
Trusted_Connection=True;

Connecting to an SQL Server instance via an IP address

Data Source=192.168.1.100,1433;Network Library=DBMSSOCN;
Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

Reference : Pinal Dave (http://blog.SQLAuthority.com), ConnectionStrings

13 thoughts on “SQL SERVER – 2005 – Connection Strings For .NET

  1. Hi dave

    Good Day..

    I have code working on mysql Connection Strings For .NET. But now that code i want switch on Connection Strings For .NET and MsSql 2005 & .NET 2008 for a Client Server.

    This is my sample code working to mysql server:

    Imports MySql.Data.MySqlClient
    Imports System.Configuration

    Public Class MySQLDBAccess
    #Region “Private Shared Variables”
    Private Shared mySqlConnString As String = String.Empty ‘MySQL Connection String
    Private Shared mySqlConn As New MySqlConnection ‘MySQL Connection
    Private Shared mySqlCommand As New MySqlCommand ‘MySQL Command
    Private Shared mySqlDataAdapter As New MySqlDataAdapter ‘MySQL Data Adapter
    Private Shared mySqlDataReader As MySqlDataReader ‘MySQL Data Reader

    #End Region

    ‘For Testing Database Connection
    Public Shared Sub TestMySQLDBConnection(ByRef strErrMsg As String)

    Try
    mySqlConn = New MySqlConnection()
    mySqlConn.ConnectionString = mySqlConnString

    mySqlConn.Open()
    mySqlConn.Close()
    Catch ex As Exception
    strErrMsg = “Cannot connect to DiabNet database.”
    DBL_PRINTLOG(“MySQLDBAccess::TestMySQLDBConnection() >> ” & ex.Message)
    End Try

    End Sub

    End Class

    Private Sub btnTestConnection_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTestConnection.Click
    Dim strErrMsg As String = String.Empty
    Dim RptString As String = String.Empty

    Try
    MySQLDBAccess.connectionString = “server = ” & txtServer.Text & “;” _
    & “user id = ” & txtUserID.Text & “;” _
    & “password = ” & txtPassword.Text & “;” _
    & “database = ” & txtDatabase.Text & “;” _
    & “Allow Zero Datetime=true;”

    MySQLDBAccess.TestMySQLDBConnection(strErrMsg)

    If strErrMsg = String.Empty Then
    ‘Write DB Connection Setting to app.config
    WriteDBConfig(MySQLDBAccess.connectionString)
    MessageBox.Show(“Test connection successful.”, “DiabNet DB Connection”, MessageBoxButtons.OK, MessageBoxIcon.Information)
    btnOK.Enabled = True
    Else
    MessageBox.Show(strErrMsg, “DiabNet DB Connection”, MessageBoxButtons.OK, MessageBoxIcon.Information)
    End If
    Catch ex As Exception
    DBL_PRINTLOG(“DS_frmDatabaseSettings::btnTestConnection_Click() Exception >> ” & ex.Message)
    End Try
    End Sub

    Please help me how to change my connection String from mysql to Mssql 2005 & Vs2008. ASAP!

    Thank and Advanced..

    Gregfox

    Like

  2. Connection strings should be set in Visual Studio 2005/8 in MY Project Settings Tab. Name the string and choose connection string, click elipse to the right and the gui will allow you to set your connection string. If you have the mysql .net connector it will work the same way.

    Then you can call in your code.

    Dim myconnstring As String = My.Settings.STIConnectionstring

    Dim sconn As New SqlConnection With {.ConnectionString = myconnstring}

    Like

  3. Hi ,I need some help for connection string sintax using sql server 2005
    I know that if i use SQl express edition i have to write: server=.\sqlExpress
    and if i use sql server 2005 i have to write : server=myServerAdress , (??) ,But what means myServerAdress ? i’ve tried with : server=dell2 (name of my computer) or server=sql or server=sql server 2005 or server= C\mydocuments\programs\sql and all of this have failed

    can you help me?

    Like

    • myServerAddress = ServerName\InstanceName

      E.g. If you were working with MSDE 2000 you have a server name, probably (local), and no instance name. In this case myServerAddress=(local)

      If you are working with SQL Server 2005 express edition, you have a server name and a named instance, having installed using the defaults, the server name would be the name of your computer and the instance name would be SQLEXPRESS (or something similar), so myServerAddress=PCName\SQLEXPRESS

      Like

  4. Hi,
    Thank you for for a very helpful website.
    I do have a question regarding the connection string. Are there any changes required on a connection string to move a database to a different drive.

    thanks,
    aein

    Like

  5. Hi Aein,

    No changes required in connection string. Because connection strings contains the database name and where it reside does not matter for calling application.

    Regards,
    Pinal Dave

    Like

  6. Hi Aein,

    Can you tell me the difference bwtween these two connection string in case of SQL Server 2005 Performance .
    Or what effects will be on Query Execution if no of connections will be increased..

    Please reply..
    I m worried about the slow performance of SQL Server 2005.

    What should I prefare??

    Regards,
    Victory

    Like

  7. Dear Pinaldave,

    We have around 400 user’s accesing our application. Application fully based on Sql server 2000 (stored procedures). And mean while we have refreshable excel sheet by the way of connecting datasource. Some of user utilizing as wrongly to update,Insert & delete using datasource sql query. I want to restict all users in excel sheet to update,delete & insert. User might be view the records only. How to restirct? Have any solution. I hope if I got means …it’s really benefical. tq

    Like

  8. I have used connection string as mentioned below, But it throwing error

    Data Source=192.168.111.254,1433;Network Library=DBMSSOCN;
    Initial Catalog=Qbill;User ID=sa;

    I am using MS SQl 2005 with VS 2008

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s