December 17, 2013 at 8:24 am
how about some middle ground? give them view / user permissions and not admin. If they find a problem, they have you fix it. Almost all work can be done without RDC. If they were reading this thread, I'd just advise them to make a ticket to you and e-mail their request every time they need work done that they cannot do. It's what you're wanting them to do so it shouldn't be an issue.
That being said... I much prefer to have SA access to my SQL servers. I already control all the data, I already have access to most of the system including registry control depending on how you lock down the service accounts.... It is easier on me to manage it top to bottom.
IF the server breaks Christmas night, now you both get called in to fix it since they need to verify the database and you have to bring it up. If one of you are missing... now it's down even longer.
Side note: You're paying an awful lot for a DBA or set of DBAs you don't trust not to break your server. Middle ground of use read access to the server can help them help you.
.
December 17, 2013 at 9:14 am
I have to come down on the side of RDP access for DBAs.
I have seen many cases where there was a problem with the server and the Windows admins claimed there was no problem. For example, a Windows admin claimed they saw nothing wrong with disk performance when I was able to show that the IO performance was only about 5% of expected throughput.
DBAs often have as much server knowledge as Windows admins because of their greater experience, and may be more motivated to actually find the problem.
IT is a team and you don't want to keep you best players on the bench when there is a problem.
I think you will find that very few DBAs a reckless enough to cause problems with the server, any more that Windows admins are reckless. DBAs tend to have a very conservative approach to these things.
December 17, 2013 at 9:38 am
I am still not seeing what functions on the server they would need. If they can work in SSMS from their machine, why do they need to login to the Server to use SSMS their? It seems like I would be creating more risk in the environment by letting them login to the machine. Are their resources on the server they need access to?
December 17, 2013 at 9:45 am
defyant_2004 (12/17/2013)
I am still not seeing what functions on the server they would need. If they can work in SSMS from their machine, why do they need to login to the Server to use SSMS their? It seems like I would be creating more risk in the environment by letting them login to the machine. Are their resources on the server they need access to?
99.9999% of the time you can do nearly everything with SSMS. It is that small percentage of things, which is usually during an outage, that you need access to the box. Paranoia seems to be more important than teamwork in your shop. The bottom line is that there is no right answer here. That should be obvious from the myriad of opinions you have received so far. If preventing RDP access to the DBA works in your environment then that is what you should do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 17, 2013 at 9:59 am
defyant_2004 (12/17/2013)
I am still not seeing what functions on the server they would need. If they can work in SSMS from their machine, why do they need to login to the Server to use SSMS their? It seems like I would be creating more risk in the environment by letting them login to the machine. Are their resources on the server they need access to?
The thing to think about from a DBA perspective is that we're neither fish nor fowl. We're not developers, yet we work with the development teams and are a major part of most development projects and development deployments. We're not sys admins, yet we have to work within the arena of servers and drives and shares and security that the sysadmins deploy. We straddle both these worlds, yet neither is too crazy about having us around AND yet neither wants to do our jobs.
So, for a DBA, it's easier (not necessarily better, not necessarily required) to just have sysadmin rights. I get done what I need without having to bug the sysadmin's who don't want to talk to me anyway. So, a common example from my past. Log backups fail (or, a new database was created and log backups were never enabled for it) and suddenly the drive where the logs are stored is full. It's 3AM. If I have sysadmin rights, I quickly create another network share, map the server to it, add a log file to my database, and figure out how to fix things from there. If I don't have sysadmin I... 1) Call the sysadmin person and tell them 1/2 of what has to happen, right flipping now 2) Cheat by shrinking other log files in order to free enough space to then get the backup of the full log done. Option 2 is actually much more time consuming and inherently less safe, but it may be the path I take because, let's say, in the past I didn't get quick responses from the admin team.
There are several bad examples at play here (why would we let a database get on the server without log backups, don't we have drive space monitoring, log backup monitoring, log size monitoring, etc.), but, you don't have to meet all the bad examples to understand how that 3AM issue (and it's always 3AM) arises. I worked without sysadmin rights for close to 10 years as the on-call DBA. It's absolutely doable. It's just not as easy.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 17, 2013 at 10:45 am
the question has been answered that your DBA can work without RDP.
What's been addressed is that you now have twice as much work to do since they cannot.
Do you have your own projects? How do you prioritize your time vs the DBAs?
If he needs:
A folder created for a new database.
Drive space checked.
Their SQL Server service restarted.
A registry key changed to add it to the MSX node.
Move a backup and the transaction logs to the mirrored site.
Verify backups are getting to your DR site.
3 AM move the log files.
Look at the system log files to see why SQL failed.
Troubleshoot quicker why that mission critical database that your company lives off of is down.
Do you drop everything to support them in a major outage? San crashing, network being affected by the gods of Asgard, servers coming up without the san and needing to be hand held back into production... You're now doing all that on your own while your DBA is asking for you to move the backup he has from one server to another to get your DR site up. He can have it back up while you keep fighting the fires else where. There's countless reasons it would make your life and your DBA's life better. They don't even need SA... Give them view of the OS drives and full control of the database drives. There is a middle ground. ^.^'
.
December 17, 2013 at 11:05 am
Here are some examples from my own experience, where I have had to use RDP:
1) Get a call from the users that the application is very slow, and I can't connect from SSMS either. It could be that SQL Server is maxed out, or the box is hung. Hopefully, the former will be limited by the resource governor or WSRM, and then I can log on to the server to investigate further.
2) Good luck loading large event or SQL Server logs over a network.
3) Let's say I have to upload MPS reports to Microsoft. Not really practical to copy huge files to my workstation.
4) If a flat file has to copied from server A to server B to be loaded into SQL Server, then I am going to have to log on to one of them directly, or everything has to go thru my workstation.
As mentioned above, these problems are most likely to occur off hours. Adding a VPN to the equation slows things down even more. And then there is the communication issue. I am already on the phone with someone for the initial problem. Now I have to call my server admin. Maybe I can use Skype for one of the calls, except that it may cause something else to time out. And will your third shift be adequately trained to support DBA's, or will they eventually have to call you as well?
So bottom line, does your management understand and accept the additional downtime and lack of productivity that will result from the DBA's not being fully able to do their job?
December 17, 2013 at 11:37 am
defyant_2004 (12/17/2013)
I am still not seeing what functions on the server they would need. If they can work in SSMS from their machine, why do they need to login to the Server to use SSMS their? It seems like I would be creating more risk in the environment by letting them login to the machine. Are their resources on the server they need access to?
I have given some examples but you seem to be ignoring them, perhaps because they do not fit the answer you want?
iptech reiterates one in point 4), and its true of any database related file.
point 1) is valid for the DAC as well which I mentioned
These are the things I tend to access when local to the server to complete my duties (note, none are SSMS)
sql configuration manager
explorer
event viewer
component services
computer management
security policies
cluster administrator
perfmon
BIDS
I am always very careful when doing this and only touch the parts I understand, by nature DBAs are cautious and I am also lazy and don't want to create problems and therefore extra work for myself.
Some of these your DBAs won't need as they relate to SSIS or SSRS, but that might change one day.
---------------------------------------------------------------------
December 17, 2013 at 11:45 am
I'd love to see the DBA from their shop chime in. ^.^
I've worked without RDP. I can tell you there are real down sides. Downtimes are longer, less people working on the same problem, single points of failure(you getting hit by a bus... per say), fixes take more coordination, and more that aren't on the front of my brain right now.
If you don't trust the DBAs that already control all of your data... Then this shouldn't be a question. Just tell them no, let them come to you with every end user style problem such as *I can't see the drive structure... where's this folder?* and go about your day. It is more work for them, for you, and more down time for everyone. If this isn't SIPR/Secret or higher, I see no reason security should be held that tight within the company as well. It's bad for retention. ^.~
.
December 17, 2013 at 12:45 pm
Well, I guess I will need to give this some consideration. They have been asking for it for a long time now. I assumed that SSMS gave them all the tools they needed. I read a couple articles that seemed to indicate that. I strive for an extremely clean, organized server that is the same across the board. I just don't like the idea of them having access to make changes on my server. Thanks for all the input. I will make a decision over the next couple of weeks.
December 17, 2013 at 12:48 pm
From what you describe in the original post, yes, the DBA's can probably do everything described without RDP access. You can view the event log, file sizes, etc. etc. without RDP access. That is going to require some work from the server team, however.
It really sounds like some education is required. The server team needs to better understand exactly what the DBA's do for a living, and the DBA's need to understand the requirements of security, auditing, etc. etc.
Once this education occurs, an assessment of the practices on both sides regarding compliance, documentation, and so forth should occur. Bring in someone from a different department as a referee!
We have admin privileges on all of the database servers, and additionally have been granted the rights on the physical hosts for the virtualized boxes.
The DBA's rarely use RDP to get into a server. But, we work together as a team and follow the rules set forth by all parties.
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/
December 17, 2013 at 9:36 pm
I'd like to take the time and say two things.
1) Thank you for coming here and asking DBA's in the field for their opinions.
2) Thank you to the community for treating the question with an honest response and kindness.
I love seeing a forum that handles itself correctly.
.
December 18, 2013 at 10:36 am
As a DBA, I would be more afraid of what the server administrators could do to the SQL Server since they don't understand the internals.
December 19, 2013 at 10:52 am
Here's a small, stupid, recent example where I needed RDP access: Making a registry change so that OPENROWSET() calls can read in pipe-delimited files (instead of comma-delimited) for a large ETL project and then changing it back. (SQL 2005 Enterprise, I sure hope this has been changed in more recent releases to accommodate other delimiters without a registry change!)
Here's a URL[/url] in case anyone else needs to know how to do this, by the way.
This is a completely candid and non-sarcastic comment coming up, though it may sound like snark: as a former sysadmin and current DBA, I'd be much more concerned about a sysadmin accessing a SQL server than the other way around. Why? I know few SQL DBAs who aren't familiar with Windows server OS's, but I know lots of Windows sysadmins who know nothing about SQL Server. ("We needed to re-boot the server after a Windows update. Was that a bad time for your 10-million-row ETL process?")
I will encourage you to think about this as a collaboration, not a competition. You and your DBAs -- TOGETHER -- hold the keys to the kingdom: your company's data. Your users (from clerks to CIO) expect your systems to be available, efficient, and secure. Ask yourself how you and your DBAs can best achieve that?
Trust is important. I work in a small shop and our sysadmin has full, unfettered access to the SQL Servers. Yet he never reboots or even logs into one without talking to me first, b/c he knows that he doesn't know what he's doing and wants to make sure that systems continue to function. I, in turn, don't make any Windows- or VM-related changes (extending disk volumes, allocating add'l RAM from the VM farm, etc.) without discussing it with him first.
Kudos to you for asking the question and not simply shutting off your DBAs.
Rich
December 20, 2013 at 3:28 am
I'd have to agree that removing tha ability to remotely log on to the server will hinder the ability for the DBA to do their work.
As long as SQL Server is running properly they could log on to the SQL server using SSMS, enable cmdexec and run all of those nasty changes on the server in any event so I don't see where you are gaining any security benifit.
If SQL server is running slowly or has crashed then they will need to identify why and the ability to get to the OS to diagnose the problem will greatly assist this. I have examined the event logs via SSMS but when it can take a minute to scroll the log rather than virtually instantaneously when logged on to the server itself there is no contest.
Bear in mind that the occassions they will need access to the server are generally the same occassions where accessing things via SSMS is a problem.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply