April 11, 2018 at 8:19 am
hurricaneDBA - Wednesday, April 11, 2018 7:55 AMHi Sue The link you sent me states at the bottom:Program the client to connect using a custom connection string.So we will have all applications using custom connection strings and hence no need to utilize the browser service. Is that manageable? Kal
How are you going to do that with SQL Server Agent? And will you only connect to the DAC using sqlcmd? Aliases? Changing global ports and implementing that as a standard for the company?
You can certainly try a lot of things to get it all work and spend quite a bit of time, change some things at the infrastructure/network level, none of this would be standard so you'd want to have all of this documented, etc. Personally I wouldn't waste the energy as it's typically just creating a lot of unnecessary confusion and complexity. I would never want to be troubleshooting connectivity issues.
Sue
April 11, 2018 at 9:08 am
Sue_H - Wednesday, April 11, 2018 8:19 AMhurricaneDBA - Wednesday, April 11, 2018 7:55 AMHi Sue The link you sent me states at the bottom:Program the client to connect using a custom connection string.So we will have all applications using custom connection strings and hence no need to utilize the browser service. Is that manageable? KalHow are you going to do that with SQL Server Agent? And will you only connect to the DAC using sqlcmd? Aliases? Changing global ports and implementing that as a standard for the company?
You can certainly try a lot of things to get it all work and spend quite a bit of time, change some things at the infrastructure/network level, none of this would be standard so you'd want to have all of this documented, etc. Personally I wouldn't waste the energy as it's typically just creating a lot of unnecessary confusion and complexity. I would never want to be troubleshooting connectivity issues.Sue
do not set a default instance to anything other than TCP1433
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 11, 2018 at 9:32 am
Perry Whittle - Wednesday, April 11, 2018 9:08 AMSue_H - Wednesday, April 11, 2018 8:19 AMhurricaneDBA - Wednesday, April 11, 2018 7:55 AMHi Sue The link you sent me states at the bottom:Program the client to connect using a custom connection string.So we will have all applications using custom connection strings and hence no need to utilize the browser service. Is that manageable? KalHow are you going to do that with SQL Server Agent? And will you only connect to the DAC using sqlcmd? Aliases? Changing global ports and implementing that as a standard for the company?
You can certainly try a lot of things to get it all work and spend quite a bit of time, change some things at the infrastructure/network level, none of this would be standard so you'd want to have all of this documented, etc. Personally I wouldn't waste the energy as it's typically just creating a lot of unnecessary confusion and complexity. I would never want to be troubleshooting connectivity issues.Sue
do not set a default instance to anything other than TCP1433
I wasn't saying to do it - but Kal wants to without having the browser running. In theory you can. And in practice most end up with a mess. That was my point.
On the other hand though - I have been at companies where they have a policy to not use the default ports as some security person believes it to be more secure. And maybe never heard of a port scanner. I don't know if that's the case here or not.
Sue
April 11, 2018 at 10:02 am
I can convince our ciso to keep the default port instead of 1433 but I need justification
You guys are the experts so is what I’m doing considered secure or a waste of time and why?
Kal
April 11, 2018 at 12:05 pm
hurricaneDBA - Wednesday, April 11, 2018 10:02 AMI can convince our ciso to keep the default port instead of 1433 but I need justification You guys are the experts so is what I’m doing considered secure or a waste of time and why? Kal
My reference to it not being a good use of time is the combination using non-default ports, disabling the browser and disabling shared memory. The issues would be what you have right now and what is in the links provided.
It's incredibly easy to find the port SQL Server is listening on.
Shared memory is used for local connections only . If someone has unauthorized physical access to the servers, you probably have more to worry about than who is connecting to the SQL Server instance on that box. If someone is authorized and is not trusted then you still have other issues in them ever being authorized in the first place.
I can't think of any additional information on the browser than what you already have.
Sue
April 11, 2018 at 12:49 pm
So what would be your recommended approach?
By the way I kept shared memory enabled because I didn’t use aliases and the agent runs with it enabled
Kal
April 11, 2018 at 1:02 pm
hurricaneDBA - Wednesday, April 11, 2018 12:49 PMSo what would be your recommended approach?By the way I kept shared memory enabled because I didn’t use aliases and the agent runs with it enabled Kal
The sql server browser is not active for a default instance of sql server ( it gets installed as disabled ), so port resolution doesn't occur. You can set the instance to use another port but you need to create an alias for the instance or connect via
IPAddress, port
Computername, port
1433 is the accepted default for sql server, as long as the instance is secured there's no issue with using the port.
When sql server is unsecured the problems arise
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 11, 2018 at 1:29 pm
Hi perry
So when you say secured I’m guessing I need to follow the CIS benchmarks and security guidelines / checklists from MS.
Anything I need to follow?
I’ll discuss the port issue with my ciso tomorrow
Kal
April 11, 2018 at 1:33 pm
hurricaneDBA - Wednesday, April 11, 2018 1:29 PMHi perrySo when you say secured I’m guessing I need to follow the CIS benchmarks and security guidelines / checklists from MS. Anything I need to follow?I’ll discuss the port issue with my ciso tomorrow Kal
windows only auth where possible.
No sql accounts with weak passwords and elevated permissions, etc
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 11, 2018 at 10:22 pm
Sue_H - Wednesday, April 11, 2018 8:19 AMhurricaneDBA - Wednesday, April 11, 2018 7:55 AMHi Sue The link you sent me states at the bottom:Program the client to connect using a custom connection string.So we will have all applications using custom connection strings and hence no need to utilize the browser service. Is that manageable? KalHow are you going to do that with SQL Server Agent? And will you only connect to the DAC using sqlcmd? Aliases? Changing global ports and implementing that as a standard for the company?
You can certainly try a lot of things to get it all work and spend quite a bit of time, change some things at the infrastructure/network level, none of this would be standard so you'd want to have all of this documented, etc. Personally I wouldn't waste the energy as it's typically just creating a lot of unnecessary confusion and complexity. I would never want to be troubleshooting connectivity issues.Sue
Sue i forgot to mention this earlier, our CISO wants to disable DAC also but i will inform him that DAC is needed for troubleshooting an nonresponsive instance. To connect to ssms ill need to use:
FQDN,PORT and click Encrypt connection but is this recommended while using DAC?
Kal
April 11, 2018 at 10:25 pm
Perry Whittle - Wednesday, April 11, 2018 1:02 PMhurricaneDBA - Wednesday, April 11, 2018 12:49 PMSo what would be your recommended approach?By the way I kept shared memory enabled because I didn’t use aliases and the agent runs with it enabled KalThe sql server browser is not active for a default instance of sql server ( it gets installed as disabled ), so port resolution doesn't occur. You can set the instance to use another port but you need to create an alias for the instance or connect via
IPAddress, port
Computername, port1433 is the accepted default for sql server, as long as the instance is secured there's no issue with using the port.
When sql server is unsecured the problems arise
Perry
Why do i need to use an Alias if using another port? Cant the agent work fine just by keeping the shared memory protocol enabled? I mean if i disable shared memory then yes an Alias is required but then the Agent jobs wont fire anymore from my testing atleast i dont know if anyone else faced the same issue.
Kal
April 12, 2018 at 12:27 am
So here is what i have been able to find from my extensive testing in the past couple of days.
SQL server agent doesnt run if you disable both Shared and Named Pipes protocol
If you check in the log files i get the error:
Date 4/12/2018 8:55:34 AM
Log SQL Server Agent (Current - 4/12/2018 8:55:00 AM)
Message
[165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
Date 4/12/2018 8:55:34 AM
Log SQL Server Agent (Current - 4/12/2018 8:55:00 AM)
Message
[298] SQLServer Error: 2, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
Solutions i have tried:
1. Create an alias and place that in the connections section in the agent and that works given that you must first enable Agent XPs from the advanced options otherwise there is no way to enter the alias into the agent properties
2. Enter the hostname into the registry as some other posts have suggested and that didnt work for me
3. Enable the Named Pipes or the Share Memory protocols and those worked
4. Update the ODBC driver to version 13.1 and this doesnt work if you have installed SSMS 17.* because that also updates the ODBC driver to version 2017.*
The main question here is the Agent's dependancy on Named Pipes and Shared Memory to connect locally to the SQL server engine? Can we edit something somewhere to make it connect using TCP/IP?
I am not familiar with using Aliases hence is the reason i am not fond of going that route
From my experience usually more applications use the Named Pipe protocol so we can disable the shared memory protocol
Any comments?
Kal
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply