Automating SQL Data Dump and SFTP File Transfer with Powershell and WinSCP on Windows

We work with a third party that takes care of a few systems for us that we automated and to make that work we had to script an automated process to export data from our SQL server and automatically upload it to an SFTP Server.

For this setup we are using WINSCP for doing the file transfer and a combination of a bat file and powershell for exporting the data out of SQL and automating the process.  I am using windows for automating this process.

What will be required:

WINSCP

Powershell

So install powershell and WINSCP on to the system.  

Open powershell as and administrator and install the Clobber the WINSCP Module and the SQLServer module

Install-Module -Name PowershellGet

Install-Module -Name SQLServer

Install-Module -Name WinSCP

Once done we will make two files, they will be running from the same directory.

SQLDataExport.ps1 and gen_sql_data.bat

The powershell file is fairly straight forward. It essentially gets and runs the sql query and saves it to a directory as a txt or csv.

SQLDataExport.ps1

$serverInstance = "$address, $port" 

$databaseName = "$databaseName" 

$Username = "$databaseUser" 

 $Password = "$databasePassword" 

$filePath = "$PATH\$FILENAME" + (Get-Date).ToString("yyyyMMddTHHmmss") + ".txt" 

Import-Module SqlServer 

$sqlQuery = "$YOURSQLQUERY" 

Invoke-Sqlcmd -ServerInstance $serverInstance -Username $Username -Password $Password -Database $databaseName -TrustServerCertificate -Query $sqlQuery | Out-File $filePath -Encoding UTF8

BatFile (This is where all the FTP Magic happens)

@ECHO OFF

powershell.exe -executionpolicy remotesigned -File $PATHTOPOWERSHELLSCRIPT.ps1

timeout /t 120

echo "FTP Started" >> seed_exReport.rpt

date /t >> exReport.rpt

time /t >> exReport.rpt

echo option batch abort > ftpPutCmds.txt

echo option confirm off >> ftpPutCmds.txt

echo open sftp://$USERNAME@$SFTPURL -privatekey=$SFTPPRIVATEKEY.ppk -passphrase=$PASSWORD -hostkey=acceptnew >> ftpPutCommands.txt

echo option transfer binary >> ftpPutCmds.txt

echo cd seed >> ftpPutCmds.txt

echo put $SQLDATA_*.txt >> ftpPutCmds.txt

echo close >> ftpPutCmds.txt

echo exit >> ftpPutCmds.txt

"C:\Program Files (x86)\WinSCP\WinSCP.exe" /console /script=ftpPutCmds.txt /log="$PATH2Error.log" /loglevel=1* /logsize=5*100M

echo "FTP Finished" >> seed_exReport.rpt

IF EXIST *.txt (move *SQLDATA_*.txt Old\) REM move old file to a folder called old

date /t >> exReport.rpt

time /t >> exReport.rpt

echo. >> exReport.rpt

Once done you can setup the task scheduler to run the batfile.  You will need the script to start in the directory that the bat file and powershell file are in.

Allowing Windows 11 Access to Unauthenticated Shares

Windows 11 prevents you from accessing open SMB shares because they are a security risk.  However sometimes you need to access these unauthenticated shares for a variety of reasons.  Here is how you do that.

Method 1: Use the Local Group Policy Editor 

1. Open the search menu and type gpedit.msc, then select "Edit group policy" to open the Local Group Policy Editor.
2. Navigate to Computer Configuration > Administrative Templates > Network > Lanman Workstation.
3. Find and double-click the policy named "Enable insecure guest logons".
4. Select "Enabled," click "Apply," and then "OK".
5. Restart your computer for the changes to take effect. 
6. 
   
   

Method 2: Use the Registry Editor 

1. Open the search menu, type regedit, and press Enter to open the Registry Editor.
2. Navigate to the following path: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\Parameters.
3. In the right-hand pane, find the AllowInsecureGuestAuth value. If it doesn't exist, right-click in an empty space, select New > DWORD (32-bit) Value, and name it AllowInsecureGuestAuth.
4. Double-click AllowInsecureGuestAuth, set the "Value data" to 1, and click "OK".
5. Close the Registry Editor and restart your computer. 
6.