A legacy VB app was working with SQL Server 2000 on an XP box, using the connection string (in an ADODC): FILE NAME=link.udl
This UDL points at the database... Provider=SQLOLEDB.1;Password=secret;Persist Security Info=True;User ID=sa;Initial Catalog=maindb;Data Source=DBXP
The database was migrated to a new Windows Server 2008 box running SQL Server 2008, and UDL file updated: Provider=SQLOLEDB.1;Password=secret;Persist Security Info=True;User ID=sa;Initial Catalog=maindb;Data Source=DB2008
The "Test Connection" button in the UDL editor is always successful, but when the application runs, the SQL Server Login dialog always appears, showing the wrong server name selected. Pulling down the list (which seems to cause the list to refresh) usually doesn't show the new server name, but if it does, or anytime I type the name in, the log-in succeeds and all is well. But next time the app runs, the log-in dialog re-appears.
(I noticed that when editing the UDL, the Refresh button for the list of SQL servers sometimes will and sometimes won't show the new server. So it looks like this server is not always being discovered. But even if we have to type the server name in, the Test button succeeds.)
SQL Browser service is running on the new server, in the Local Service account. Firewall ports were opened by hand, and also using a script from MS support site, but seem to include TCP 1433, 1434, 135, 4022, and UDP 1434. (Server is running the default instance)
Meanwhile, the application can access the database reliably if it bypasses the UDL, using the SQLOLEDB.1 connection string directly in the ADODC, but we'd rather not hard-code the server name, etc. in the app. I thought this was the reason for having a UDL.
Ideas, anyone?