0

I've created a Winform that connects to SQL Server Express x64 using Trusted Connection string.

On the development machine (Which SQL Server is running on as well) the Winform works 100%, the resulting connection string is:

Persist Security Info=False;
Integrated Security=true;
Initial Catalog=BushBreaksLodgeManager;
server=.\SQLEXPRESS

As soon as I try the same win form on a networked PC it fails to connect. On the remote machine I can see the development machine via Network places i.e. \\JOHN-WIN7PC

The remote PC's resulting connection string is:

Persist Security Info=False;
Integrated Security=true;
Initial Catalog=BushBreaksLodgeManager;
server=\\JOHN-WIN7PC\SQLEXPRESS

I have also enabled TCP-IP on SQL Server (It was set to <default>), turned off all firewalls, anti-malware (not that this should interfer) and anti-virus is not installed on either machine.

SQL database settings are as follows:
==================================================
Authentication method: Windows Authentication
Database: BushBreaksLodgeManager
ServerName: JOHN-WIN7PC\SQLEXPRESS
Instancename: SQLEXPRESS
Computername: JOHN-WIN7PC

Sql Server settings
==================================================
Mixed mode (Win auth and Sql Server)
Connections -> Allow remote connections = true

Any help in this regard would be appreciated as I am at a loss.

I have pasted the exception below:

2011/11/18 04:55:50 PM : Process: UtilsDatabase -> TestDatabaseConnection
2011/11/18 04:56:05 PM : Process: UtilsDatabase -> TestDatabaseConnection Connection failed
2011/11/18 04:56:05 PM : Settings value -> UseIntergratedSecurity: False
2011/11/18 04:56:05 PM : Settings value -> Server: \\JOHN-WIN7PC\SQLEXPRESS
2011/11/18 04:56:05 PM : Settings value -> Database name: BushBreaksLodgeManager
2011/11/18 04:56:05 PM : Connection String: Persist Security Info=False;Integrated Security=true;Initial Catalog=BushBreaksLodgeManager;server=\\JOHN-WIN7PC\SQLEXPRESS

==================== New Exception ====================
2011/11/18 04:56:05 PM

A network-related or instance-specific 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 and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)

.Net SqlClient Data Provider

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at BushBreaksLodgeManager.UtilsDatabase.TestDatabaseConnection()

Void OnError(System.Data.SqlClient.SqlException, Boolean)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
John Cogan
  • 51
  • 1
  • 8
  • Is this within a domain environment? has the user whom is running the remote software been created as a login/user on the server? – Alex K. Nov 18 '11 at 15:24
  • Is this a 2008 or 2005 version? I remember that in Express 2005 you had to explicitly tell it to accept remote connections. Don't know how it is in versions 2008 and 2008R2. – ssarabando Nov 18 '11 at 15:58
  • No it is purely within a single office. There is no need for security, hence the trusted connection string. They have not asked for anything elaborate. Should simply be able to connect to the server with the provision of the database name and sql server path and server name. – John Cogan Nov 18 '11 at 16:11
  • It is Sql Server 2008 R2 64 bit – John Cogan Nov 18 '11 at 16:11
  • 2
    "Trusted connection" & "Integrated Security" are designed to work in situations when all the users on the network & the server itself are part of the same (or a trusted) Windows Domain (Its the active-directory validated domain credentials that are "trusted") but in your case you seem to be in a workgroup. I remember you could use a workgroup if you recreated identical user accounts on the server with the same credentials as the workgroup users but this was a pain to get working. Perhaps it would be simpler to switch to sql authentication & create users that way. – Alex K. Nov 18 '11 at 16:39
  • Just in case, check if the "Allow remote connections to this server" is checked (within Management Studio, open your server Properties window and go to the "Connections" page). Also, Alex K. has a point too: AFAIK, if the machines don't belong to the same domain, you'll have to go through some extra hoops. Check [this](http://idunno.org/archive/2007/01/24/Using-SQL-Trusted-Connections-from-a-machine-not-in-a.aspx) and [this](http://msdn.microsoft.com/en-us/library/ms144284.aspx). You may be better off using SQL Server Authentication. – ssarabando Nov 18 '11 at 17:05
  • Just in case 2: is sql server browser running at the target machine? It is not a prerequisite for connections, but it does facilitate them. – Gert Arnold Nov 18 '11 at 18:37
  • Allow remote connections to this server is checked. I am just wonder if my remote pc can even see the server. Remote PC find my dev PC in network places but will the error message be the same whether it can see the SQL Server but not connect or not see the SQL server at all? Surely if it could see it but not connect it'd say something along the lines of invalid login or some such. – John Cogan Nov 18 '11 at 19:30
  • Gert I dont want to have to install any SQL stuff other than items allowing connections on all pcs, the people that will install and use this are not tech savvy in the least. – John Cogan Nov 18 '11 at 19:34
  • Alternatively, you can try to connect to the server via an IP address. It's been a while since I've tried that approach, but I think it can work. If it works, you might want to set the server to use a static IP and reconfigure your user's connection strings. – RLH Nov 18 '11 at 19:48
  • Ok I have changed the Win form to use a connection string using Integrated security, i.e. username and password. I've added a user to the SQL Server security and to the database etc and tested it on the dev machine without issue. Now on the remote PC I still cannot connect to the SQL Server instance on the development machine. Can see the dev machine from this other pc but cannot seem to find the SQL instance. I have double checked that the SQL server allows remote connections and both named pipes and TCP/IP is enabled. – John Cogan Nov 20 '11 at 08:54
  • Even trying to connect using SQL Server Management Studio returns the same error, so this is a networking/SQL issue not a coding one. – John Cogan Nov 20 '11 at 08:55

1 Answers1

0

I think the error is very simple. When connecting to SQL Server you don't prefix the server name with \\, So instead of server=\\JOHN-WIN7PC\SQLEXPRESS you should use server=JOHN-WIN7PC\SQLEXPRESS.

Krzysztof Kozielczyk
  • 5,887
  • 37
  • 28