3

I have two Windows Server 2012 R2 servers in a workgroup, on the first one I have MS SQL Server, the other is used (among other things) as a storage for backups. On the database server both the SQL Server Database Engine and SQL Server Agent run with default virtual accounts - NT Service\MSSQLSERVER and NT Service\SQLSERVERAGENT.

Now, I want to use Ola Hallengren maintenance jobs to backup my MS SQL Server databases. These scripts are wrapped into a SQL Server Agent job but the backup is a t-sql procedure thus executed by the SQL Server Database Engine with NT Service\MSSQLSERVER.

I've created an account on the storage server and a shared folder with access permissions for this account. I'm able to connect the share on the database server with a net use command providing credentials for the created account. There's a catch: the share is connected for an account which runs the net use command.

If I connect the share with Local System account (which supposed to be for all users) then still NT Service\MSSQLSERVER cannot access it, neither any other account can. Seems like the hack previously proposed on stackoverflow is fixed in Windows Server 2012 R2.

And if I run the net use command as a step in SQL Server Agent job, then it is done with the other virtual account - NT Service\SQLSERVERAGENT - and the backup step fails with "folder not found" error.

So I would like to know a way to create a Windows service account with all the necessary permissions for the SQL Server Database Engine service to be used in a workgroup environment. Or an explanation why it cannot be done. Ideally it should be a script - Powershell or VBScript.

I tried to run both SQL Server Dabase Engine and SQL Server Agent as Local System and the backup worked like a charm. But I would not consider this as a solution because it's not recommended from a security perspective.

Also I would not consider a solution to run the net use command with xp_cmdshell due to the same security reasons.

Community
  • 1
  • 1
prot
  • 220
  • 4
  • 21
  • How are you accessing the share, what is the path you are using? Have you mapped the drive or are you using UNC paths? – DavidG Nov 30 '16 at 11:49
  • @DavidG, I use UNC path, like this: `net use \\remotehost\folder password /user:remotehost\username` – prot Nov 30 '16 at 14:06
  • The problem is that this is only valid for the currently logged in user, you need to do that while logged in as the same user that SQL runs as. – DavidG Nov 30 '16 at 14:07
  • Yeah, that's the goal. What I need is a script to create such an account in a workgroup so that the account will be able to run MS SQL Server and connect a network shared folder. Or some kind of a manual, if not a script. – prot Dec 01 '16 at 06:17

3 Answers3

0

Okay, the correct way to get it done is to do the following:

  • Create a user account on both servers with exactly the same name. This can be done if the user is named .\UserName rather than ServerName\UserName.
  • Once it is done, you need to sniff permissions of a virtual service account NT Service\MSSQLSERVER. And this can be done with the use of SubInACL utility. This tool is created by Microsoft exactly for the purpose and can be downloaded from official Microsoft Download Center. Or you can skip sniffing the actual permissions and proceed to the next point.
  • All of the permissions described in the knowledge base article https://msdn.microsoft.com/en-us/library/ms143504.aspx can be set in place for the newly created account with the same SubInACL tool. There are several articles across the Internet on how to use the tool, I used this one - https://redmondmag.com/articles/2008/03/01/dive-deep-with-subinacl.aspx
  • The last steps will be: launch SQL Server service under the newly created user account and provide access to the shared folder on the other server.

Probably this should go to superuser.com

prot
  • 220
  • 4
  • 21
-1

Will it be possible to use an SSISpackage to do the following:

  1. Create and script via tsql with the login-details,etc. and export it to a script file on disk.
  2. Run the run the script.
  3. Delete the script file again.

Example: https://www.simple-talk.com/sql/ssis/adding-the-script-task-to-your-ssis-packages/

To create batch file using T-sql:

--Set first day of Week to Monday
--Value First day of the week is 
--1 Monday 
--2 Tuesday 
--3 Wednesday 
--4 Thursday 
--5 Friday 
--6 Saturday 
--7 (default, U.S. English) Sunday 
SET DATEFIRST 1

Declare @CmdSource varchar(100),
    @CmdDestination varchar(100),
    @Year varchar(4),
    @Week varchar (2),
    @Difference int

Set @Difference = 0
Set @Year = Convert(varchar(4), DatePart(Year, GetDate()-@Difference))
Set @Week = Convert(varchar(2), DatePart(week, GetDate()-@Difference))
If @Year = '2010'
BEGIN
    Set @Week = @Week - 1
END

SELECT @Week = 
        CASE Len(@Week)
            WHEN 1
            THEN '0' + @Week
            else @Week
        END

Set @CmdSource = 'XCopy "<sourcepath>' + @Year + '\extras text' + @Year + @Week + '.bak" '
Set @CmdDestination = '"<Destination path>" /Y'

SELECT @CmdSource + @CmdDestination as Batchfile

Export to file

Flat files connection

We opted for creating the file at a set time. We then setup a separate windows scheduled task to run independently from SQL at a set time after the creation. Running with SSIS at that time 2010 was unpredictable. There is only one very long destination column (length 200, up to you how long.)

Danie Schoeman
  • 160
  • 8
  • Could you please elaborate "Create and script via tsql with the login-details". I can create an SSIS package with a C#/VB.net script. No problem here. But I'm not sure that I understand what is the code inside it should be. Your answer seems more a question than an answer. – prot Dec 01 '16 at 12:49
  • We did something like that in the past. I am still looking for it. Will get back to you with and example and steps – Danie Schoeman Dec 01 '16 at 13:57
  • Updated answer to include steps and images with more detail. Hope is helps – Danie Schoeman Dec 01 '16 at 14:23
  • What?! How does this answer my question? It's completely off-topic. – prot Dec 02 '16 at 07:38
  • It a suggestion.to use if as part of a solution. To back it up on the one copy it's across. do what ever next. It is some steps in a solution not the solution. it should not be neccessary for me to explain that. – Danie Schoeman Dec 02 '16 at 07:44
  • I spoke to a friend a lot better versed on the network side. The right way to do it is to setup a domain. Join PC's to the domain. Most of the stuff is then integrated. It is then not required to apply hacks. – Danie Schoeman Dec 02 '16 at 07:50
  • The use of a domain completely undermines the original question. I know that it can easily be done in a domain environment. The lack of a domain is a part of the problem I'm asking how to solve. – prot Dec 02 '16 at 08:01
  • As time progress al lot of hacks that originally worked is patched and does not work anymore. There still is some work arounds that still work but require more work to setup and maintain. You decide. – Danie Schoeman Dec 02 '16 at 08:04
-1

IMHO, your best bet still is to run net use \\remotehost\folder password /user:remotehost\username in xp_cmdshell.

You only need to run it once and then can close off xp_cmdshell again if you don't like it; it's not like changing that option requires a reboot =)

For that I'd suggest to put this into 'startup' procedure. You can even add WITH RECOMPILE if you're afraid someone will sp_helptext it to find the pwd of that login. Then again, people might freak out when finding a startup proc that's encrypted =)

USE master
GO
CREATE PROCEDURE sp_net_use_that_other_server
AS

-- To allow advanced options to be changed.  
EXEC sp_configure 'show advanced options', 1;  

-- To update the currently configured value for advanced options.  
RECONFIGURE;  

-- To enable the feature.  
EXEC sp_configure 'xp_cmdshell', 1;  

-- To update the currently configured value for this feature.  
RECONFIGURE;  

-- you might want to fetch this dynamically from the database somewhere, or simply leave it hardcoded here...

EXEC master..xp_cmdshell 'net use \\remotehost\folder password /user:remotehost\username'

-- To disable the feature.  
EXEC sp_configure 'xp_cmdshell', 0;  

-- To update the currently configured value for this feature.  
RECONFIGURE;  


Return
GO

-- set this up as a startup procedure
EXEC sp_procoption @ProcName = 'sp_net_use_that_other_server'   
                 , @OptionName =  'startup'   
                 , @OptionValue = 'on';  

GO

After that, the database engine should be able to BACKUP DATABASE... to that share.

deroby
  • 5,576
  • 2
  • 16
  • 28
  • The use of xp_cmdshell poses a security risk and generally not recommended. Please check this link https://blogs.msdn.microsoft.com/sqlsecurity/2008/01/10/xp_cmdshell – prot Oct 10 '17 at 03:39
  • Hence why I suggest to 'close that hole' again right after you don't need it anymore; you only need it to create the share while starting up the service. – deroby Oct 12 '17 at 09:06