November 12, 2014 at 8:25 am
Hi,
I have an existing 2012 default SQL Express. It's set up on a VPS managed by a third party. I have an administrator account on this 2012 Windows server. I'm not much of a sysadmin or a DBA but I get around. 😉
I've installed a new NAMED instance on this VPS and can not connect to it with client tools (SSMS). If I remote in, I can connect this way.
Question
What steps might a seasoned DBA expect to make when getting a new named instance ready for the world.
Assign a port? Check the port?
Open the firewall for the port?
Will this new named instance listen on a different port than the previously installed SQL Express instance?
Thanks
John
SQL 2012 Standard VPS Windows 2012 Server Standard
November 12, 2014 at 8:35 am
By default, named instances listen on a dynamic port.
You can change that from the Configuration Manager.
Here's an article on the subject: http://blogs.msdn.com/b/arvindsh/archive/2012/09/08/how-to-assign-a-static-port-to-a-sql-server-named-instance-and-avoid-a-common-pitfall.aspx
Once you configure the static port, open incoming traffic on the firewall.
Please note that the SQL Browser service translates instance names into ports, so the client application will need to connecto to the UDP port 1434, which has to be allowed on the firewall as well.
Another option is connecting to the specific port without specifying the named instance. In this case, the browser service is not contacted.
-- Gianluca Sartori
November 12, 2014 at 8:45 am
Johnny B (11/12/2014)
Hi,I have an existing 2012 default SQL Express. It's set up on a VPS managed by a third party. I have an administrator account on this 2012 Windows server. I'm not much of a sysadmin or a DBA but I get around. 😉
I've installed a new NAMED instance on this VPS and can not connect to it with client tools (SSMS). If I remote in, I can connect this way.
What is the exact error you are getting?
If you are using Windows Authentication, has your login been granted access to the SQL Server?
Question
What steps might a seasoned DBA expect to make when getting a new named instance ready for the world.
Assign a port? Check the port?
Open the firewall for the port?
Maybe. Yes. Maybe.
By default, SQL uses port 1433. You can verify this by opening "SQL Configuration Manager" and looking at the TCP/IP configuration.
If you want to use a different port, then you would specify it here.
Logging in successfully will check the port. Not sure what you mean here.
If there are firewalls in place that need to have connections passed through to be able to access SQL Server, then sure, you would need to open the firewall.
Normally, this is not something a DBA would do. We would call the network folks!
Will this new named instance listen on a different port than the previously installed SQL Express instance?
Thanks
John
Named instances use a dynamically configured port.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 12, 2014 at 8:59 am
Ok so I've learned named instances use a dynamically assigned port.
Before learning this I assigned a port in configuration manager adding a port under IPALL. I turned off the SQL Express service and took that port 1433. It did not work.
I've put things back and now need to know how to open up the firewall to the new named instance when the port is dynamic.
SQL 2012 Standard VPS Windows 2012 Server Standard
November 12, 2014 at 9:06 am
I've put things back and now need to know how to open up the firewall to the new named instance when the port is dynamic.
You don't. You assign a port to the instance, and then you open the firewall.
Not being a network person, there is likely another way to do this, but that is what we do here.
One of the "best practices" IMHO, is to always change the default port from 1433.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 12, 2014 at 9:17 am
I assigned the default port to the named instance and disabled SQL Express. I took it's identity. 🙂 So now the developers just use the same connection credentials.
But does using the default port put us at risk to brute force attacks? We get them a lot.
SQL 2012 Standard VPS Windows 2012 Server Standard
November 12, 2014 at 11:34 am
Google "port 1433". The vulnerabilities are endless....
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply