6

I am trying to use what appears to be a rather badly written application which connects to an SQL Server database which I specify.

I cannot seem to connect to the SQL Server instance I wish to use without specifying the port with a comma e.g. DBServer\DBInstance,<port_number>

Now, the problem that this causes, and why I think the application is badly written, is that for some reason the application then thinks that the port number is the name of the database, and the server\instance name is DBServer\DBInstance. I believe it is not expecting to have to deal with a port number like this.

Now, I have used SQL Server instances before which did not require the port number to be specified. I know this must be some setting under the TCP/IP settings in SQL Server Configuration manager, and I have tried setting the port to the default 1433 as well as turning on dynamic ports, but neither of these have worked.

I am still fairly new to SQL Server, if anybody could point me in the right direction it would be greatly appreciated.

Edit

Having solved the problem as per Ste Bov's answer, I also now know why the application could not handle the port being specified. Internally, the application was storing its data in a temporary csv file and didn't bother to check input for commas or enclose it in quotes, so when it came to the port in the csv it thought it was the next field which was supposed to be the Database Name.

So yes, a badly written application indeed!

  • 1
    how about using a Hostname Alias for a SQL Server Instance ? – Kin Shah Oct 19 '15 at 13:27
  • does the application require port in the connection string? if so dont use dynamic ports (port will change on SQL Server restart) and SQL Browser isnt the answer. Statically assign port in Configuration manager and connect with hostname,port as Max suggested – Bob Klimes Oct 19 '15 at 13:46
  • @BobKlimes No, I believe the application cannot handle the port being specified in the string. Hence why I believe it is badly written. – Luke Oct 19 '15 at 13:48
  • 1
    @luke in that case SQL Browser with connection of dbserver\dbinstance or statically set the port to 1433 and just connect with dbserver ( no port or instance name). – Bob Klimes Oct 19 '15 at 13:59
  • @Kin That would be a nice solution but seems to require access to systems I do not have access to. – Luke Oct 19 '15 at 14:00
9

If you have named instances and don't want to declare your ports then you need to ensure that SQL Server Browser is running

By default this runs through port UDP 1434

  • I turned the Server Browser on but I am still having problems. – Luke Oct 19 '15 at 13:26
  • This worked, thanks! Turns out I needed to open UDP port 1434. – Luke Oct 19 '15 at 13:54
  • 1
    Have you opened UDP port 1434 on the firewall for the server? EDIT: Too late, you already found it! – Peter Elzinga Oct 19 '15 at 13:56
5

@Ste Bov is correct, enable the SQL Server Browser Service on the server running SQL Server; however if for some reason you don't want to or cannot use that service, you can connect to the instance using the IP address and port number, like TCP:x.x.x.x,y where x.x.x.x is the IP address, and y is the port. You can alternatively specify the connection as TCP:hostname,port, where hostname is just the server name (i.e. do not include the instance name).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.