patseguin Global Moderator Posted February 10, 2016 Global Moderator Share Posted February 10, 2016 I had this database on my 2008 Server and am moving it to a Windows 7 workstation for a few reasons. Anyways, I created a new instance called DESIGNS and restored my database to it. I created the udl and did a test connection and everything is fine. I coped the udl to 2 workstations on the domain but it won't connect. In fact, in the server name drop down list it doesn't even list the instance which is SERVICE1/DESIGNS. It lists all the computers on the domain and a few other SQL instances but not designs. I tried typing it manually and did a test connection but it times out. I ran the tool on the sql machine and verified that remote access is enabled and I even rebooted to make sure all services restarted. What have I done wrong that the instance won't show up on any workstations? Link to comment Share on other sites More sharing options...
c.grz Posted February 10, 2016 Share Posted February 10, 2016 Firewall on SERVICE1? Link to comment Share on other sites More sharing options...
patseguin Global Moderator Posted February 10, 2016 Author Global Moderator Share Posted February 10, 2016 11 minutes ago, c.grz said: Firewall on SERVICE1? OFF Link to comment Share on other sites More sharing options...
sc302 Veteran Posted February 10, 2016 Veteran Share Posted February 10, 2016 you have it working on 1 workstation but not another? Is the one workstation that you have it working on a separate workstation from the workstation hosting the db? Link to comment Share on other sites More sharing options...
Skiver Veteran Posted February 10, 2016 Veteran Share Posted February 10, 2016 Have you tried just doing a simple ODBC test to the SQL instance to see if it gives you a more meaningful error? If it's not firewall I'd be tempted to check things like Named Pipes being enabled or SQL Browser service running. Chances are they have nothing to do with remote connections into SQL but I know I've had some weird issues in the past with SQL and playing with those have helped, my SQL knowledge is pretty basic so I imagine there are some DBA's out there laughing at these suggestions patseguin 1 Share Link to comment Share on other sites More sharing options...
patseguin Global Moderator Posted February 10, 2016 Author Global Moderator Share Posted February 10, 2016 2 minutes ago, sc302 said: you have it working on 1 workstation but not another? Is the one workstation that you have it working on a separate workstation from the workstation hosting the db? No. I only have it working on the Windows 7 workstation where the SQL instance is installed. On all of the workstations in the domain, the instance is not listed when I try to select it. I can see the SQL instances on my Windows 2008 server, just not the one on the Windows 7 workstation. the udl tests OK on the Windows 7 box and I triple verified that remote access is enabled. Link to comment Share on other sites More sharing options...
AStaUK Posted February 10, 2016 Share Posted February 10, 2016 (edited) As Skiver has said I think you need the SQL Server Browser service enabled and you also need to make sure that TCP/IP is enabled under the SQL Config Manager (SQL Server Network Config). Like Skiver my SQL knowledge is basic, but each time I have setup a new instance these are the things I have had to enable to make it show. Link to comment Share on other sites More sharing options...
patseguin Global Moderator Posted February 10, 2016 Author Global Moderator Share Posted February 10, 2016 7 minutes ago, AStalUK said: As Skiver has said I think you need the SQL Server Browser service enabled and you also need to make sure that TCP/IP is enabled under the SQL Config Manager (SQL Server Network Config). Like Skiver my SQL knowledge is basic, but each time I have setup a new instance these are the things I have had to enable to make it show. Browser service on the SQL hosted machine or the workstations? The workstations must already have it because they can already browse SQL connections. They just don't see the one on the Windows 7 box. And I do have TCP/IP enabled. Link to comment Share on other sites More sharing options...
AStaUK Posted February 10, 2016 Share Posted February 10, 2016 18 minutes ago, patseguin said: Browser service on the SQL hosted machine or the workstations? The workstations must already have it because they can already browse SQL connections. They just don't see the one on the Windows 7 box. And I do have TCP/IP enabled. It would be on the SQL hosted PC. https://technet.microsoft.com/en-us/library/ms181087(v=sql.105).aspx Link to comment Share on other sites More sharing options...
limok Posted February 10, 2016 Share Posted February 10, 2016 You need to specify port number to be used for tcp it's usually 1433. make sure you set this in the client protocols for tcp/ip and use this script for firewall settings netsh advfirewall firewall add rule name="Open Port 80" dir=in action=allow protocol=TCP localport=80 @echo ========= SQL Server Ports =================== @echo Enabling SQLServer default instance port 1433 netsh advfirewall firewall add rule name="SQL Server" dir=in action=allow protocol=TCP localport=1433 @echo Enabling Dedicated Admin Connection port 1434 netsh advfirewall firewall add rule name="SQL Admin Connection" dir=in action=allow protocol=TCP localport=1434 @echo Enabling Conventional SQL Server Service Broker port 4022 netsh advfirewall firewall add rule name="SQL Service Broker" dir=in action=allow protocol=TCP localport=4022 @echo Enabling Transact SQL/RPC port 135 netsh advfirewall firewall add rule name="SQL Debugger/RPC" dir=in action=allow protocol=TCP localport=135 @echo ========= Analysis Services Ports ============== @echo Enabling SSAS Default Instance port 2383 netsh advfirewall firewall add rule name="Analysis Services" dir=in action=allow protocol=TCP localport=2383 @echo Enabling SQL Server Browser Service port 2382 netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow protocol=TCP localport=2382 @echo ========= Misc Applications ============== @echo Enabling HTTP port 80 netsh advfirewall firewall add rule name="HTTP" dir=in action=allow protocol=TCP localport=80 @echo Enabling SSL port 443 netsh advfirewall firewall add rule name="SSL" dir=in action=allow protocol=TCP localport=443 @echo Enabling port for SQL Server Browser Service's 'Browse' Button netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow protocol=UDP localport=1434 @echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK) netsh firewall set multicastbroadcastresponse ENABLE This is what I do most times I install a new SQL server. Link to comment Share on other sites More sharing options...
Skiver Veteran Posted February 10, 2016 Veteran Share Posted February 10, 2016 1433 and 1434 to my knowledge are the default ports. Link to comment Share on other sites More sharing options...
limok Posted February 10, 2016 Share Posted February 10, 2016 5 minutes ago, Skiver said: 1433 and 1434 to my knowledge are the default ports. Yes, but these need to be bound to an IP address also Link to comment Share on other sites More sharing options...
patseguin Global Moderator Posted February 10, 2016 Author Global Moderator Share Posted February 10, 2016 2 hours ago, Skiver said: Have you tried just doing a simple ODBC test to the SQL instance to see if it gives you a more meaningful error? If it's not firewall I'd be tempted to check things like Named Pipes being enabled or SQL Browser service running. Chances are they have nothing to do with remote connections into SQL but I know I've had some weird issues in the past with SQL and playing with those have helped, my SQL knowledge is pretty basic so I imagine there are some DBA's out there laughing at these suggestions This fixed it, thanks! Nothing to do with ports. Once I enabled the browser service on the SQL computer within a couple minutes it showed up on the workstations. Thanks!! Link to comment Share on other sites More sharing options...
patseguin Global Moderator Posted February 10, 2016 Author Global Moderator Share Posted February 10, 2016 Of course there is still something wrong now. When I recorded a design to the database, it copies the actual files to folders that I defined. On this new SQL setup, it is not copying the files for some reason. I'm about to just give up. Link to comment Share on other sites More sharing options...
c.grz Posted February 10, 2016 Share Posted February 10, 2016 11 minutes ago, patseguin said: Of course there is still something wrong now. When I recorded a design to the database, it copies the actual files to folders that I defined. On this new SQL setup, it is not copying the files for some reason. I'm about to just give up. What is actually directing the files to the folder? Are you using a front end application to connect to the database? Have you looked at the old location to see if the files for the designs you're recording to the new database are being saved there? Link to comment Share on other sites More sharing options...
Recommended Posts