1

Trying to debug a customer's connection problem when they try specifying the SQL Server port.

Reproducing the issue locally I checked and my server runs under the default 1433 port. Specifying that port using:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "servername,1433";
builder.InitialCatalog = databaseName;
builder.IntegratedSecurity = false;
builder.UserID = userName;
builder.Password = UserPsw;

This fails with "Login failed for user". If I remove the port number, it connects fine. Why does this happen?

By the way the same happens with Windows Integrated Security.

And one more piece of information: I do run also a SQL Server 2005 express SQL server on the same box.

zukanta
  • 2,691
  • 2
  • 19
  • 25
  • Are SQL Server Logins or Mixed Mode Authentication enabled (as opposed to just Windows Authentication mode)? What happens if you don't specify the port number? Why not use a const connection string? – Dai Aug 05 '14 at 19:19
  • yes because as I said above by removing only the port number everything connects fine. – zukanta Aug 05 '14 at 19:20
  • 1
    Please correct me if I'm wrong, but aren't port numbers specified by `hostname:port` instead of `hostname,port`? (I don't know about SQL server, just going off knowledge of URLs.) – Jashaszun Aug 05 '14 at 19:21
  • 1
    @Jashaszun, for url yes, [but not for sql server](http://stackoverflow.com/a/89583/1043380) – gunr2171 Aug 05 '14 at 19:22
  • Verify that that is the correct port. I had a case where someone changed the port for some reason. – L_7337 Aug 05 '14 at 19:25
  • Take a look at [this post](http://stackoverflow.com/questions/19525939/sql-server-named-instance-remote-connections-without-port) and [this post](http://stackoverflow.com/questions/18795863/cant-connect-to-sql-2012-remotely-by-ip-and-named-instance), might help. – gunr2171 Aug 05 '14 at 19:27
  • Is it possible you have multiple instances? – Aaron Bertrand Aug 05 '14 at 19:27
  • check the sql server configuration manager – Mike Cheel Aug 05 '14 at 19:28
  • `SELECT local_tcp_port FROM sys.dm_exec_connections WHERE local_tcp_port IS NOT NULL` to verify the port number. – Dave Mason Aug 05 '14 at 19:31
  • the port is correct, it's the one specified in SQL Server Config Manager. Moreover, if I mess it up with a different port, I get a server not found sort of error. So yes 1433 is correct. – zukanta Aug 05 '14 at 19:34
  • So what does the connection string of the SqlConnection object look like at run time in the debugger? – Dave Mason Aug 05 '14 at 19:39
  • the conn string looks fine: "Data Source=server\\sqlexpress2008,1433;Initial Catalog=mydb;Integrated Security=False;User ID=myuser;Password=mypsw" – zukanta Aug 05 '14 at 19:44
  • Hmmm. Any difference if you use the full qualified server name? `Data Source=server.YourDomain.com\\sqlexpress2008,1433;Initial Catalog=...` – Dave Mason Aug 05 '14 at 19:58
  • @Aaron Bertrand, yes I do run also a SQL Server 2005 express on the same box. Modifed my post to reflect this. – zukanta Aug 05 '14 at 20:05
  • So my guess is that when you specify the port you are connecting to the 2008 Express instance, and when you don't you are connecting to the 2005 Express instance. Or maybe vice-versa. But that would certainly explain why a particular login would only work in one case. I recommend not using just `servername` in the connection string, and using the same connection string in all cases (SSMS, your app, sqlcmd, etc). – Aaron Bertrand Aug 05 '14 at 20:11

1 Answers1

4

If you are using SQL Server Express and a named instance then the port will not be 1433 but rather a dynamically assigned port.

See this article, scroll down to the section 'Ports used by SQL Server' and look for 'SQL Server named instances in the default configuration'.

http://msdn.microsoft.com/en-us/library/cc646023.aspx

I don't know if you can assign specific ports to SQL Server Express instances. If so, that is what you would need to do, and specify that port in your connection string. If it is not possible to assign a specific port to a to SQL Server Express named instance then you would have to stick with specifying the instance name, without a port.

user469104
  • 1,206
  • 12
  • 15
  • wooohoo perfect that worked. 1433 was specified as the default port and everything hinted at this being the port being used but I fixed the port using the instructions at : http://msdn.microsoft.com/en-us/library/ms345327(v=sql.105).aspx and now it connects to my fixed port. My take on this is that before it would sometimes use 1433 and sometimes not...thanks! – zukanta Aug 05 '14 at 20:20