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!

What credentials (windows user) is your application running under? — Matthew
– Matthew, Commented May 12, 2013 at 3:20
try to connect with connection string parameters what you have given in your application by using Server Explorer — Damith
– Damith, Commented May 12, 2013 at 3:20
Hmm.. I didn't know you could connect using a domain login and password - I always thought that you needed a trusted connection. I could be wrong on that. Assuming that this is a legitimate way to connect, you should try using back slash instead of forward slash in your connection string user id: ...("user id=domain\username... — chue x
– chue x, Commented May 12, 2013 at 3:23
Try getting rid of the trusted connection portion from your connection string. "Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;" — tsells
– tsells, Commented May 12, 2013 at 4:11

Amit Mittal · Accepted Answer · 2013-05-12 06:01:42Z

1

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.

answered May 12, 2013 at 6:01

Amit Mittal

1,14711 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1944272 Over a year ago

Thanks Amit, It is working now. (The mistake was mine see my comment above.) I will give you this one because it is good to know that I can use "." instead of the server name. BTW server=.\<instance-name> results in "Unrecognized escape sequence", however server=.\\<instance-name> works. Maybe "\" is an escape sequence for C#. Thank you for your reply.

Amit Mittal Over a year ago

Yes, Backslash is an escape sequence in C# which give special meanings within a string, like \n means new line character etc. That is why we've to use double backslash in cases we want to use single backslash in c# strings. I am sorry for the confusion caused due to single backslash in above connection string.

k3b · Accepted Answer · 2013-05-12 06:11:02Z

0

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

edited May 12, 2013 at 6:11

answered May 12, 2013 at 6:05

k3b

14.8k7 gold badges56 silver badges88 bronze badges

Collectives™ on Stack Overflow

Connect to SQL Server using C# code fails

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related