Backup and Restore Plan
-
I need to backup and restore 10 databases from a MSSQL 2008 server to an MSSQL 2014 server.
Below is my plan of execution. I was hoping I could get some input on whether this is a good plan or not or maybe missing something.
I tried it on a test database and it seemed to run smoothly.
Thanks!
Here is the plan:
--1) ENABLE disk access
EXEC sp_configure 'show advanced options', 1RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
--2) VERIFY
RESTORE VERIFYONLYFROM DISK = N'C:\Backups\Test_Staging_20151216_102557.BAK'
WITH CHECKSUM,
--RENAME FILES HERE IF NEEDED/PATH TO NEW LOCATION
MOVE N'myDB002_dat' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL14\MSSQL\DATA\Test_Staging.mdf',
MOVE N'myDB002_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL14\MSSQL\DATA\Test_Staging_log.ldf'
--3) RESTORE DB WITH MOVE(move files to new locations):
USE [master]RESTORE DATABASE [Test_Staging]
FROM DISK = N'C:\Backups\Test_Staging_20151216_102557.BAK'
WITH FILE = 1,
--RENAME FILES HERE IF NEEDED/PATH TO NEW LOCATION
MOVE N'myDB002_dat' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL14\MSSQL\DATA\Test_Staging.mdf',
MOVE N'myDB002_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL14\MSSQL\DATA\Test_Staging_log.ldf',
CHECKSUM, REPLACE, STATS = 10
--4) generates sql command to find orphaned users in each user database
USE masterSELECT 'USE '+ name+ ' EXEC sp_change_users_login ''Report'' '
FROM sys.databases
WHERE name not in ('master','msdb','model','distribution','tempdb')
ORDER BY name
--5) execute script on specified database
USE Test_Staging EXEC sp_change_users_login 'Report'--6) Try autofix on orphaned user
EXEC sp_change_users_login 'Auto_Fix' , 'testUser';--7) Specify password if needed
USE [Test_Staging] EXEC sp_change_users_login 'Auto_Fix','testUser',NULL ,'testUser12345'--8) Verify that user is no longer orphaned
USE Test_Staging EXEC sp_change_users_login 'Report'--10) Check Database Integrity
DBCC CHECKDB (Test_Staging) WITH NO_INFOMSGS;--11) Turn off disk access
EXEC sp_configure 'show advanced options', 1RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
-
Personally, I'm a BIG fan of xp_CmdShell and I might be missing something but... I don't see you actually using it anywhere in your scripts.
Also, it might give everyone the nice warm fuzzies but disabling it will not protect you. Only the same people (sysadmin or control server privs) that can turn it on can use it. If someone bad gets in, having it disable won't even slow them down if they want to use it. Of course, if they get in with sysadmin privs, they probably won't need to use it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yeah, you don't need step 1 and 11 for this work.
While we are on the topic I recommend avoiding xp_cmdshell. I believe it sets a bad tone from a design perspective and leads to too many security concerns to make it worth using. Plus there are tools out there with more features capable of managing a distributed service-oriented environment, .NET, PowerShell or SSIS to name a few.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Actually, preaching to disable cmdshell is a very out-dated concept. There are lots of ways you can hurt security, but cmdshell isn't really one of them. Here's a link to my blog on the subject.
http://www.midnightdba.com/DBARant/security-theater/[/url]
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Jeff Moden (12/16/2015)
Personally, I'm a BIG fan of xp_CmdShell and I might be missing something but... I don't see you actually using it anywhere in your scripts.Oh ok, I was under the impression that 'xp_cmdshell' was required/used whenever you interact with the file system. Is this not the case?
Thanks!
-
No, you don't HAVE to have cmdshell when you work with the filesystem. There are a couple XPs to do something simple things, and you can write CLR. Otherwise, y, cmdshell is your only option. And it's the best option too. Seriously, read my blog above on why cmdshell isn't the security hole you think it is. Basically it's a product of fear mongering by people who don't really know what they're doing.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
KenpoDBA (12/17/2015)
No, you don't HAVE to have cmdshell when you work with the filesystem. There are a couple XPs to do something simple things, and you can write CLR. Otherwise, y, cmdshell is your only option. And it's the best option too. Seriously, read my blog above on why cmdshell isn't the security hole you think it is. Basically it's a product of fear mongering by people who don't really know what they're doing.@Midnight, Thanks for sharing your rant. I loved your use of "theater", a far underused word.
Not that all of what you said was directed towards me, or maybe all or none of it was I couldn't tell, but I felt compelled to write a bit about some of your comments. I am very familiar with xp_cmdshell and what it can and cannot do. I have built extensive solutions using it (before I knew better) and also worked to eradicate it from many environments. I am also well aware of the ins and outs of the overall security debate and its characterization as a security hole as well as security theater. Honestly I think it ranks somewhere in between high-danger and completely safe. The bottom line is, I would not recommend it for new development. That's not preaching, that's my recommendation. It is not fear mongering, I just think there are better tools available that set a better tone as it relates to security. This is also taking into account the way the market is moving towards cloud-based solutions with virtualized hardware and managed coding environments leaving xp_cmdshell an odd-tool-out.
You mention in your blog post about rights elevation which is really talking about identity impersonation and that identity having more rights than the caller. One other aspect of impersonation I do not think you mentioned is where someone carries out a task as if they were running under the engine service account without having had to supply that service account's password. You cannot get away without entering a password when using RunAs or at a Windows Login prompt but you can with xp_cmdshell...I take issue with that. There are other ways to do that with SQL Server, namely SQL Agent, but at least we have the option to keep that service disabled if we choose. We can also do some things with SQLCLR regarding getting out to the OS as the engine service account but at least with SQLCLR you can code things to run under the caller's own Windows Credential whereas with xp_cmdshell you either get the engine service account or the one proxy account. While the proxy account may be a less-privileged account than the engine service account you still can't tell who is who once the request leaves SQL Server and hits the OS layer, or beyond the OS when hitting network filers or other servers.
For my time and money I think there are better tools out there than xp_cmdshell largely because I do not like designs where all control flow is handled in T-SQL, of which xp_cmdshell quickly becomes a necessity when you design that way and then you have platform lock. For me, control flow belongs in a managed language like .NET, SSIS or PowerShell outside the confines of a T-SQL context. It would be nice if Microsoft put a little more of their money where their mouth was on security and made xp_cmdshell (among other risky features like SQLCLR) separate installation options so at least we can choose not to even have them available on our instances.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Orlando,
That was a very nice, well thought-out objection... overruled (move quote). Anyway, I can tell you've at least thought about this before and at least you've made your decision based off of real consideration and not just blind fear. But no, none of my previous comments were directed directly at you, but you did get me going on one of my rants again. You can tell I do that a lot.
That said though, I think you've come to the wrong conclusion, and here's why.
Sometimes you've really got no good way to get at the info you need or to do something you need w/o cmdshell. Sure, it's physically possible to use CLR but for some of these things you've got to throw your DB into UNTRUSTWORTHY which is far worse than anything cmdshell can do to you. Not only that, but having CLR enabled on all your DBs on all your servers also isn't tenable. It's just too much CLR. And now even for simple things I've got to have managed code? That's an awful low of managed code because I wanted to check something on the file system real quick. And making changes now becomes this ridiculous deployment process. So no, CLR isn't the answer either.
Right about now you'll be asking for an example of something that you would need cmdshell for... something that makes it worth it. Let's take an SP I wrote where I needed to find some table properties. Many table properties are hard to come by and some object properties are almost impossible to come by. But in SMO they're easy. So I could spend something like 75 lines of code piecing together the info I need into different #tables, or I could just cmdshell out and get it with SMO in a single call. And some properties are only reasonably available in SMO. What if I need to check something on another server? I'm certainly not going to use a linked server now am I? So again I can cmdshell out and call that other server and get my info.
And yeah, it's true that once that request leaves SQL and goes to the OS, you don't know who made the call. But you don't need to really. You don't know who made the call on any other processes like that either. SQL obscures all of its OS-level calls from you. And if you really want to know who's using that code, then simply log the user info and the call they made to a table before they make it. You wrote the SP afterall, you can do stuff like that. And remember, I'm advocating simply making cmdshell available for admins and admin processes. I think for user processes that there can be better solutions. The issue there is that if user1 needs to do something on the OS so you give him cmdshell and you assign the proxy perms just for that task, and then user2 needs to also do something completely different from user1, and you give the proxy those perms too, well now user1 and user2 share perms. They were only supposed to be able to do their own thing and now they can do the same things. So user1 could abuse his new rights if he wanted, etc. So for users there can a lot of times be better solutions. But for admins... admins can do anything they want anyway. Ordinary users aren't going to have access to cmdshell unless you give it to them. So to say you're going to get rid of cmdshell in the entire shop and not let anybody use it... well, I think that's just short-sided.
And again, why pick on cmdshell? There are many other things in sql that are dangerous, yet you're not going on a quest to completely get rid of all of them either are you? Look at what trouble SSIS pkgs can be. I've seen users write pkgs that did nothing like what they were supposed to and caused quite a bit of damage at the OS level. But nobody's saying let's get rid of SSIS completely and not use it at all. Even CLR can do lots of damage, yet you're actually advocating that as a solution. And if you leave your DB in TRUSTWORTHY mode, then how do you do those OS-level things that need doing? SSIS isn't always viable. Business reqs can be quite complex, and processes need to be quicker than firing up an SSIS pkg. And sure, CLR is harder for users to get their heads around so they're not as likely to do it, but when they do they could instantly throw you into memory pressure and severely affect the system. Not to mention, there's an old saying in security. Actually I got it from chess but we also say it here too. "Never rely on the stupidity of your opponent". So something being a harder solution for users to implement simply isn't a viable security measure. A much better measure is to simply not allow users to push SPs into your DB to begin with. This way you can review everything they do.
And let's face it, while you say you can just turn off the Agent if you don't think it's secure enough, let's be real. I've only seen a small handful of servers ever that could do that. Even the simplest of servers still needs to run backup and other maint. So you really can't just turn off the Agent.
Ok, this is getting really long so I'll stop now. But I'm going to leave you with this. While you have your facts down, I think you've come to the wrong conclusion. Killing cmdshell in the entire shop isn't the answer. Leaving it open to admins is an excellent way to perform your tasks w/o having to jump through ridiculous hoops. Just don't give anyone else cmdshell if you don't want. But for admins... hell yeah. That's all I'm really saying. It isn't that cmdshell isn't w/o its concerns. Only a fool would say that. But it can also be done quite safely if left to just admins, who can do anything they need anyway. So don't get rid of cmdshell because it's too useful. But definitely tightly control its use.
I love discussions like this though. Maybe I've changed your mind and maybe you've changed mine. The point is we're having this debate and it's forcing both of us to consider our views again. And maybe something one of us said will seep into the other one's head and slowly start to make a difference. Or maybe it just solidified both of our opinions. Either way, the discussion is vital.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Magy, Jeff and Orlando are right in that you don't need xp_cmdshell enabled for what you have posted.
On the overall xp_cmdshell debate, I've seen the debates and arguments on both sides. I know that outdated versions aren't being patched any more. I've read papers and seen multiple presentations. With this, like everything, I try to look under the hood and address the underlying reasons objectively. In a manner of speaking, I "check my emotions at the door" and approach the situation with logic and reason.
I have to come down on the side that it's safe to use when configured properly. Believe it or not, it can be configured so you don't have to give individual logins permissions to run it directly. I use it myself and find it incredibly useful to accomplish tasks without having to build an overly-complicated solution in multiple environments using multiple languages. Things tend to get complicated all by themselves; we don't need to help them along and accumulate technical debt in the process.
Looking at the SQL Server security configuration whitepaper, Microsoft says the following:
System stored procedures such as xp_cmdshell or sp_send_dbmail are off by default and should remain disabled unless there is a reason to use them.
The paper goes on to talk about it not being necessary and using the CLR, but then we're back to multiple languages and environments again. The language of the database is T-SQL and I prefer it over the alphabet soup of acronyms: SSIS, SSAS, SSRS and whatever else comes along. There are plenty of things in any database that could be a risk, but I don't hear about people saying that we should keep sp_send_dbmail turned off, even though the paper has it in the same sentence. So, if there's a reason to use them, use them.
I think the crux of the debate comes down to security on the server. I got to see a SQL Saturday presentation on hacking SQL Server. It's scary how easy it is to break in using a known SQL login. The attacker doesn't even have to know SQL inside and out because there are tools for everything. Given that sa is a well-known login with sysadmin privs and given that SQL logins can be hacked, it's a ridiculously-simply decision to disable the sa login and leave it disabled.
If sa is enabled and someone breaks in using it, it doesn't take more than a couple of ms to enable it. If they're in as sa, they have permission to do this. Who else (other than sysadmin) has permission to do this? Nobody. BTW, the tool attempts to turn on xp_cmdshell and it took all of 3 ms to do it.
Also, if someone breaks in with sysadmin privs, let's say they don't use xp_cmdshell at all. What can they do without it? Well, they can only read every table in every database. What's left? Hearing about the incident on the news.
Edit: There may very well be aspects to the debate of which I'm not aware. If that's the case, I'm certainly open to changing my position. Thus far, I haven't seen any.
-
You can avoid using sp_change_users_login by transferring the logins one time using sp_help_revlogin (see https://support.microsoft.com/en-us/kb/918992)
You can also use the Use the Copy Database Wizard to copy the databases once or to create SQL Agent job for this. (See https://msdn.microsoft.com/en-us/library/ms188664.aspx)
You can query the backupset and mediafamily tables inside the msdb database to get the most recent backup filenames and other useful data.
-
@Midnight, I think your understanding of the need to enable TRUSTWORTHY to use a SQLCLR assembly is misguided. Have a look at assembly signing when you get a chance. I have lots of SQLCLR assemblies granted EXTERNAL_ACCESS and none of my databases have the TRUSTWORTHY bit enabled. In my case I implemented these SQLCLR objects as a retrofit in order to get rid of xp_cmdshell because it was too expensive to shift the entirety of the control flow out of T-SQL for the particular application. Moving to SQLCLR was a compromise I made and while I was not able to move the control flow out of the database I was able to disable xp_cmdshell in the environment and it has introduced a tighter set of control over who is doing what from the database to the OS level. In my estimation, if you are thinking you need EXTERNAL_ACCESS or UNSAFE for an assembly then the app-design might be getting away from you. In some cases it could be warranted but for me it requires a much higher level of justification to go in that direction.
As for picking on xp_cmdshell...I do not limit myself there. You'll be excited to know that I also do not like OPENROWSET, OPENQUERY, Linked Servers, the BULK INSERT T-SQL command, EXTERNAL_ACCESS or UNSAFE SQLCLR usage, and the list goes on. Basically anything that leads to folks designing the entirety of their control flow into T-SQL or positioning the database engine as an application server engine, I avoid it. Most of the techniques surrounding these tools come with security obfuscation or impersonation issues which is where I start to take issue with them.
Most people use Agent but it's not required. Enterprise schedulers and centralized backup tools can be used and for massive environments it's a viable approach not to run Agent everywhere and have centralized servers kicking off backups and running maintenance jobs, likely via C# or PowerShell + SMO I might add.
You also did not address the impending cloudiness of what we do. How do you reconcile continuing down the path you are going when Microsoft deprecated Extended Stored Procedures a while back and tools like xp_cmdshell and BULK INSERT (basically anything that wants to go to a physical location from a T-SQL context) are being left out of Azure?
@Ed, if you think you are avoiding the alphabet soup of SSRS, SSIS, etc. by doing everything in the language of the database then I would ask you to take a harder look at the application call stack when you use xp_cmdshell. By definition xp_cmdshell brings you into another language. In and of itself xp_cmdshell does nothing but act a conduit for us to run cmd-shell code, i.e. Windows Batch commands, which is already a different language from T-SQL. From there people get even more creative and call powershell.exe so they can run PowerShell scripts, or call cscript.exe so they can run VB Script. Some even store their PowerShell commands in a table and pass them down to xp_cmdshell dynamically. Heck, some folks decide it's a good idea to shell out so they can turn around and reach right back into the database with bcp.exe or sqlcmd.exe. From my point of view this comes across as absurd behavior when the processing could have been done much more easily and straightforward using something more suitable for control flow like a .NET console app, SSIS or PowerShell (in the first place). Beginning with xp_cmdshell for admin tasks is fairly benign however it quickly leads to canning the "move this backup file over there" into a proc and eventually (because we are developers, right?) to elaborate solutions similar to what I mentioned above which broadens the need to grant service accounts more permissions and has more people running commands as someone other than themselves. I know my position is an unpopular one but that's how I see things.
Please forgive me if my tone is escalating but as you can tell I am passionate about this topic. It's a tough one to articulate but that may be why I continue to try. I agree 100% Midnight that it is vital. Vital to security and good application design and I truly believe this: vital to the survival of the platform. People should start learn all the facts, look at what others are doing to broaden exposure to real-world use cases and implementations and then make the decision from there. I am not attacking anyone's position. I am merely trying to explain mine. Any offense is not deliberate.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Orlando Colamatteo (12/20/2015)
@Ed, if you think you are avoiding the alphabet soup of SSRS, SSIS, etc. by doing everything in the language of the database then I would ask you to take a harder look at the application call stack when you use xp_cmdshell. By definition xp_cmdshell brings you into another language. In and of itself xp_cmdshell does nothing but act a conduit for us to run cmd-shell code, i.e. Windows Batch commands, which is already a different language from T-SQL. From there people get even more creative and call powershell.exe so they can run PowerShell scripts, or call cscript.exe so they can run VB Script. Some even store their PowerShell commands in a table and pass them down to xp_cmdshell dynamically. Heck, some folks decide it's a good idea to shell out so they can turn around and reach right back into the database with bcp.exe or sqlcmd.exe. From my point of view this comes across as absurd behavior when the processing could have been done much more easily and straightforward using something more suitable for control flow like a .NET console app, SSIS or PowerShell (in the first place). Beginning with xp_cmdshell for admin tasks is fairly benign however it quickly leads to canning the "move this backup file over there" into a proc and eventually (because we are developers, right?) to elaborate solutions similar to what I mentioned above which broadens the need to grant service accounts more permissions and has more people running commands as someone other than themselves. I know my position is an unpopular one but that's how I see things.I agree with you that people misuse it, just like everything else. That's why I don't do it. And I don't use PowerShell. I use xp_cmdshell for simple things like getting a list of files in a directory, cleaning things up on the server and copying today's daily data file from a supplier to a date-stamped version for import. The cool part is that you can get the output of the command into a temp table directly without having to jump through the hoops necessary in PowerShell. It delivers the data where it's needed - the database. That's where I'm doing the work and that's where I need the data.
Is DOS a separate language? Absolutely and I'll never argue the point any other way.
However, the main point I was trying to make about the whole thing is that people feel it's a security best practice to leave it turned off when it's really nothing more than a panacea and won't stop anyone. If an attacker breaks in with sysadmin privs, it doesn't matter if it's turned off or not because they'll just turn it back on. Further, they don't even have to think about it because the attack tool does it for them. The real point is to disable the sa login. Period.
It isn't like they need xp_cmdshell to do damage. Consider this: If someone broke into your server with sysadmin privs and physically destroyed your hardware, you're out the cost of the server. You (hopefully) have your database backups, so the data loss will be minimal. That's dwarfed by the cost of the stolen data. There's credit monitoring ($20 per year per person for 2 years), bad press and the resulting lost business and the flood of lawsuits that are likely to come your way. This is the real cost.
Orlando Colamatteo (12/20/2015)
Please forgive me if my tone is escalating but as you can tell I am passionate about this topic. It's a tough one to articulate but that may be why I continue to try. I agree 100% Midnight that it is vital. Vital to security and good application design and I truly believe this: vital to the survival of the platform. People should start learn all the facts, look at what others are doing to broaden exposure to real-world use cases and implementations and then make the decision from there. I am not attacking anyone's position. I am merely trying to explain mine. Any offense is not deliberate.I hear you and thank you. We agree that it's vital. Please understand that I'm not trying to change your mind on this at all. I'm only trying to emphasize the importance of considering the whole picture. I know of no reason to have the sa login enabled. Disabling it also settles most of the debate about xp_cmdshell.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply