June 22, 2014 at 3:58 pm
I have installed a SQL Server 2012 Express on a Windows 2008R2 and am struggling to connect remotely to my database.
There is no other database server on that server.
Would it be simpler to assign port 1433 as a fixed port to that named instance?
I am struggling to get this working... 🙁
In order to reproduce the problem in a "Test Environment", I have setup a Virtual Box on my laptop and can't manage to connect remotely to a named instance.
I have no problem with the default instance (after authorising 1433 through my firewall).
From internet reading, it looks like I should assign a specific address to my named instance instead of dynamic port number.
To try and use port 14330, I took the following steps
On my "virtual" server
- checked "Allow remote connections to this server"
- in SQL Server Configuration Manager, specified the port to use as 14330 for one of the IP Address interfaces (192.168.0.xx)
- added a Inbound rule in my virtual machine Windows Firewall allowing everything on TCP 14330
On my "client" machine, I created an alias pointing at port 14330 and server MyVirtualServer\InstanceName
but when I try to access my Alias through SSMS on my "client" machine, it fails to connect (timeout afer 30 seconds).
Am I missing something?
Thanks
Eric
June 23, 2014 at 4:18 am
SQL Express does not allow remote connections by default. In order to enable them, you have to enable "TCP/IP" in SQL Server Network Configuration in SQL Configuration Manager--have you done this?
June 23, 2014 at 4:25 am
edit
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 23, 2014 at 4:25 am
Eric Mamet (6/22/2014)
I have installed a SQL Server 2012 Express on a Windows 2008R2 and am struggling to connect remotely to my database.There is no other database server on that server.
Would it be simpler to assign port 1433 as a fixed port to that named instance?
I am struggling to get this working... 🙁
There is no need to assign the default port
Pick a unused port number and assign it to ther instance, you'll need to make sure remote connections are enabled within the instance and that TCP\IP is enabled via SQL Server config manager.
Eric Mamet (6/22/2014)
From internet reading, it looks like I should assign a specific address to my named instance instead of dynamic port number.
If you have a firewall shaping traffic into the server then yes set a static port for the named instance.
Eric Mamet (6/22/2014)
- in SQL Server Configuration Manager, specified the port to use as 14330 for one of the IP Address interfaces (192.168.0.xx)
Set the static port on the IPALL section and not for an individual address
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 23, 2014 at 5:48 am
Ah..... :unsure:
Set the static port on the IPALL section and not for an individual address
I only set it for a specific IP address.
Do I leave the other entries as they are (using dynamic port) or do I modify them all?
Thanks
Eric
June 23, 2014 at 6:00 am
a.Start SQL Server Configuration Manager
b.Protocols for <instance name>
c.Enable TCP/IP
d.Fix the port “IPALL” to use 1433 (or pick any other unused port)
e. Restart the SQL Server Service
f.Allow the port “inbound” on the server firewall (Domain Network Firewall)
g.Allow the same port “outbound” on the client
You may not need steps f. and g. It depends if your running windows firewall.
Steve
June 23, 2014 at 6:32 am
Eric Mamet (6/23/2014)
Ah..... :unsure:Set the static port on the IPALL section and not for an individual address
I only set it for a specific IP address.
Do I leave the other entries as they are (using dynamic port) or do I modify them all?
Thanks
Eric
Just for IPALL.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 26, 2014 at 1:49 am
Perry,
This fixed the problem on my test machine.
I did not have to create an outbound rule on the client windows firewall, though.
However, there are so many rules that there might already be one covering this.
I still have to try on my "production" machines (I only get access occasionally) but I am quite confident it should work.
Thanks a lot for your help 😀
Eric
June 26, 2014 at 4:34 am
Eric Mamet (6/26/2014)
Perry,This fixed the problem on my test machine.
I did not have to create an outbound rule on the client windows firewall, though.
However, there are so many rules that there might already be one covering this.
I still have to try on my "production" machines (I only get access occasionally) but I am quite confident it should work.
Thanks a lot for your help 😀
Eric
Then the connection was likely trying one of the other address assignments. Generally you set the static IP for all assignments, glad you're sorted.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 28, 2014 at 4:32 pm
I just tried on my prod server but I can't even logon to my SQL server from the server itself if I only enable TCP-IP.
What could I get wrong?
I "may" have messed up a bit the other IP entries.
I tried to force them all to 1433.
Any idea what to look into?
Thanks
June 28, 2014 at 5:16 pm
Can you post a screen shot of the tcpip settings dialog in configuration manager
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 29, 2014 at 10:01 am
I have attached two images.
The second one being the bottom half when scrolling down.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply