Can't create UDL connection on one computer, but can on another one. What can cause this? (sql server 2008)

Question

I have a sql 2008 server running on server 2008 r2. I can create a UDL file and successfully connect to the database from two of our workstations and from the 2008 server. We just got a new computer, and it can't connect to the database using a UDL file. The new computer replaced a workstation that was able to connect to the database by a UDL file. We are using Windows authentication to connect to the database, and I'm logging in with the same user account on all the systems i've referred to here.

What would block the UDL file from connecting to the database on one system, but not another? We're using the same windows account to authenticate and there is no type of firewall on any of the systems. All the systems are on the same lan/subnet.

Here are screenshots of the UDL connections:

This is a successful connection from one workstation.



This is a failed connection from the new workstation.

Any help would be great. I'm not that familiar with SQL, but I can post any more details about anything you need me to.

Thanks

Answer 1

Hello,

You already mentioned the two common point
- The Windows account, which may don't have permission to logon to SQL Server
- Firewall which blocks communication with SQL Server

Can you ping SERVER01 from that machine? Try to use "SERVER01, 1433" as "Server name" instead of the instance name.

---

Olaf Helper

Blog Xing

Answer 2

The Windows account must have permissions because I'm logged in as the same account on the server and the 2 workstations. It connects on the server and one workstation, but not the other workstation.

When I run netstat on the server, it's not listening on port 1433. The sqlserv.exe process is listening on port 21004, and I can telnet to that port from all the computers, including the one that can't make the UDL connection. Yes I can ping server01

Answer 3

I'm assuming the UDL file is an exact copy of one that was working before, so the problem is with something else. So here are troubleshooting steps that do not include the UDL. How to Troubleshoot Connecting to the SQL Server Database Engine You will probably find the error by going through these, but if not, suspect the UDL file.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 4

Thanks, even though netstat said the process sqlserv.exe was listening on port 21004, the sql logs say that the sql server is listening on port 11309. When I put the server name in the UDL file in this format, I can connect:

SERVER01,11309

Is that port permanent, or do they sometimes change?

Answer 5

You can configure SQL Server to always listen on the same port. See Configuring a Fixed Port http://msdn.microsoft.com/en-us/library/ms345327(SQL.105).aspx

Otherwise, when the Database Engine starts it will select an available port. It will select 1433 if that is available. Otherwise it can select something that might vary. Of course it probably will pick the same port number repeatedly, but that is not reliable. If you want to connect by using the port number (instead of dynamically getting the port number from the SQL Server Browser service), then you should configure a fixed port.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty