SQL Server 2005 Help

[patseguin Global Moderator]

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?

[c.grz]

Firewall on SERVICE1?

[patseguin Global Moderator]

  On 10/02/2016 at 16:16, c.grz said:

Firewall on SERVICE1?

Expand  

OFF

[sc302 Veteran]

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?

 

 

[Skiver Veteran]

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 Global Moderator]

  On 10/02/2016 at 16:32, 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?

 

 

Expand  

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.

[AStaUK]

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.

[patseguin Global Moderator]

  On 10/02/2016 at 16:49, 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.

Expand  

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.

[AStaUK]

  On 10/02/2016 at 16:57, 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.

Expand  

It would be on the SQL hosted PC.  https://technet.microsoft.com/en-us/library/ms181087(v=sql.105).aspx

[limok]

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.

 

[Skiver Veteran]

1433 and 1434 to my knowledge are the default ports.

[limok]

  On 10/02/2016 at 17:23, Skiver said:

1433 and 1434 to my knowledge are the default ports.

Expand  

Yes, but these need to be bound to an IP address also

 

 

 

[patseguin Global Moderator]

  On 10/02/2016 at 16:36, 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  

Expand  

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!!

[patseguin Global Moderator]

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.

[c.grz]

  On 10/02/2016 at 20:02, 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.

Expand  

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?