Connect to SQL Server using C# code fails

Question

SqlConnection fails within a Windows 2008 R2 standalone environment where SQL Server is installed with a named instance:

        SqlConnection myConnection = new SqlConnection(
            "user id=domain/username;password=pa$$word;server=MyServername/MyInstanceName;" +
            "Trusted_Connection=yes;database=MyDatabase2;connection timeout=30");

I can successfully connect to the SQL Server instance using Visual Studio’s ‘Server Explorer’ The connection string that it creates is:

Data Source= MyServername\MyInstanceName;Initial Catalog=MyDatabase2;Integrated Security=True

In my SqlConnection I have tried the following with the results shown...

server=MyServername/MyInstanceName;" +    //fails within timeout
server=MyServername\MyInstanceName;" +    //Unrecognized escape sequence
server=MyServername\\MyInstanceName;" +   //suspends far longer than timeout
server=localhost\\MyInstanceName;" +      //suspends far longer than timeout
server=localhost/MyInstanceName;" +       //fails within timeout

This is not a remote connection. VS and SQL Server are both on the same machine. How do I connect to a named instance?

This is the error message I receive:

System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-s
pecific error occurred while establishing a connection to SQL Server. The server
 was not found or was not accessible. Verify that the instance name is correct a
nd that SQL Server is configured to allow remote connections. (provider: Named P
ipes Provider, error: 40 - Could not open a connection to SQL Server) ---> Syste
m.ComponentModel.Win32Exception (0x80004005): The network name cannot be found
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception
, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj
ect stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternal
ConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Bool
ean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFa
ilover)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSn
iOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo
serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirect
edUserInstance, SqlConnectionString connectionOptions, SqlCredential credential,
 TimeoutTimer timeout)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTime
r timeout, SqlConnectionString connectionOptions, SqlCredential credential, Stri
ng newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdent
ity identity, SqlConnectionString connectionOptions, SqlCredential credential, O
bject providerInfo, String newPassword, SecureString newSecurePassword, Boolean
redirectedUserInstance, SqlConnectionString userConnectionOptions)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOp
tions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConn
ectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConn
ectionPool pool, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbCon
nectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnectionOptions
 userOptions)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnectionOp
tions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection ow
ningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean o
nlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& co
nnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection ow
ningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbCon
nectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection
 owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions
, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection
 outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1
retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at ConsoleApplication1.Program.Main(String[] args) in c:\Users\hptSetupAdmin\
Documents\Visual Studio 2012\Projects\ConsoleApplication1_ConnToSql\ConsoleAppli
cation1\Program.cs:line 33
ClientConnectionId:00000000-0000-0000-0000-000000000000

Thanks!

Answer 1

if you want to connect to sql-server by user/password instead of using integrated security

• your user needs access-rights to the windows-machine where the server is running on
• your database must be conigured to accept login via user/password
• you must grand this userer access to the sql-server and to the database you want to use.

Is your code running in a web-app? if so the iis-default-user may not have access-rights to the windows-machine where the server is running on

Answer 2

For Local Instance, better use "." instead of server name. For eg:

string constr="server=.\<instance-name>;integrated security=true;database=<db-name>";

Check if this works.