TL;DR: In the SQL Configuration Manager, set the TCP port to a static one, if is trying to use Dynamic, and remove the Dynamic port. On the Server, go into the Advanced settings for the Firewall, and set Inbound rules for both the TCP and UDP ports, allowing them to connect. On the client, set Outbound rules in the Firewall manager for the same. Support Articles at bottom of post.
So, I do third party tech support for a couple of independent schools. Several of them use a program called Raiser’s Edge to keep track of charitable donations, and solicitations. This is all well and good, and the program certainly does the job, but sometimes it makes you want to down a liter of vodka and go home.
The set up: We had to replace a machine that was hosting a networked install of a Raiser’s Edge database. We didn’t realize it was networked until we got the call that they couldn’t access it from their laptop (crap).
The initial troubleshooting: First, we needed to uninstall RE from the laptop (the client computer). It would not go. Finally decided to reboot the machine to make sure RE was not running anywhere. Suddenly, the uninstall went like a breeze.
Now we needed to install RE from the network share on the server. We can’t connect. That took turning off all the firewalls on both machines to fix, but still, we could not get to the “Deploy” folder, that should have been the only available network share on the machine.
Turns out that the installer does not set that folder to be “Available” across the network. There was no documentation for that. Set it to “Available” and boom. I can see the network share.
Run the setup.
Try to run RE.
Start getting database errors. Native Error 17 – Can’t connect to the Database. Call Support and they say “Yeah, its probably the firewall.”
I was too irritated to tell them there was no firewall turned on, at first, but when I mentioned it, they said that it was possible an antivirus had blocked the ports needed. Go to this KB article and open the ports.
I go through the directions, figure out that SQL has a dynamic port, and follow the directions for that configuration. It doesn’t work. Fantastic.
Finally, Darling Husband o’Mine says, “Why don’t you specify the port it uses?”
So in the end, this is what worked:
- In the SQL Configuration Manager, set the IPALL port to 1433
- Stop and restart the SQL service/reboot the machine (I wound up rebooting, but YMMV).
- On the Server, in Firewall Management, under Advanced Settings, set up Inbound Rules for the following:
- TCP port 1433 open (or some open port)
- UDP port 1434 open
- On the Client, set up Outbound Rules for the following:
- TCP port 1433 open (or whatever port you used on the server. THEY HAVE TO MATCH)
- UDP port 1434 open
- Install RE on the client machine from the Deploy share on the Server.
- Test the connections.
If any of this makes no sense, here is the supporting documentation for all of it:
- Native Error 17 KB article from BlackBaud (great resource, by the way): https://kb.blackbaud.com/articles/Article/42647
- How to figure out what Port your SQL Server is using: https://kb.blackbaud.com/articles/Article/39342
- Configure a Server to listen on a specific port in the SQL Configuration Manager: https://msdn.microsoft.com/en-us/library/ms177440.aspx
- How to modify the Windows Firewall for SQL Database Engine Access: https://msdn.microsoft.com/en-us/library/ms175043.aspx?f=255&MSPPError=-2147217396
Good luck, and may the force be with you on this one.