Can't get SureBackup SQL test to not fail

VMware specific discussions

Re: Can't get SureBackup SQL test to not fail

Veeam Logoby Vitaliy S. » Mon Oct 17, 2016 12:49 pm

Hi Randall, even though this does not solve your immediate problem, but Veeam Availability Orchestrator can probably solve this security issue for you, as it uses credentials manager for passing username/password to a verification script. Currently we are running private beta for this product, so feel free to ask your local SE about this.
Vitaliy S.
Veeam Software
 
Posts: 19558
Liked: 1102 times
Joined: Mon Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov

Re: Can't get SureBackup SQL test to not fail

Veeam Logoby bg.ranken » Mon Oct 17, 2016 4:30 pm

Ah, thanks for the info, I'll look into it. For now I just hard-coded the credentials into the script for SQL and it seems to work fine.

One thing I noticed though is that all arguments are saved to the SureBackup job logs. I'm sure your engineers have already thought of it but if you pass any credentials from the credential manager through Veeam Availability Orchestrator or future version of Veeam, might want to somehow mask the password from the logs.
bg.ranken
Enthusiast
 
Posts: 55
Liked: 10 times
Joined: Wed Feb 18, 2015 8:13 pm
Full Name: Randall Kender

Re: Can't get SureBackup SQL test to not fail

Veeam Logoby Vitaliy S. » Mon Oct 17, 2016 8:10 pm

Yes, we've spotted it too, passwords are not exposed in Veeam Availability Orchestrator log files.
Vitaliy S.
Veeam Software
 
Posts: 19558
Liked: 1102 times
Joined: Mon Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov

Re: Can't get SureBackup SQL test to not fail

Veeam Logoby cffit » Sun Oct 23, 2016 8:53 pm

I'm having issues with getting this to work. We have the veeam backup service account as an AD account and it has been added to our SQL server and given public permissions to all databases. When I setup the SQL script stuff in the application group under each SQL server, I choose to use the pre-defined SQL script and then under the credentials tab I select the veeam backup service account. Fails with these results in the log:

Code: Select all
[10/23/2016 3:41:33 PM] Info ====================
[10/23/2016 3:41:33 PM] Info Starting SQL checker script
[10/23/2016 3:41:33 PM] Info Enumerating SQL instances on 192.255.255.60
[10/23/2016 3:41:33 PM] Error Failed to enumerate instances on SQL server
[10/23/2016 3:41:33 PM] Info Output of the command 'sc \\192.255.255.60 query':
[10/23/2016 3:41:33 PM] Info [SC] OpenSCManager FAILED 5:Access is denied.
[10/23/2016 3:41:33 PM] Info Exit code: 2. Unable to connect SQL server


I've also tried specifying the sa account credentials by manually entering the path to the SQL script and adding the arguments with the sa account and password. That also fails.

I can run SQL MGMT Studio as the veeam backup account and see all the databases.

Any ideas?
cffit
Expert
 
Posts: 338
Liked: 33 times
Joined: Fri Jan 20, 2012 2:36 pm
Full Name: Christensen Farms

Re: Can't get SureBackup SQL test to not fail

Veeam Logoby cffit » Sun Oct 23, 2016 9:54 pm

And I should mention, I have got this to work in the past fine with SQL 2008 R2 on Windows 2008 R2 and the current version of VEEAM 9 that we are on. My current issue is with Windows 2012 R2 and SQL 2014. I'm not sure if there are differences there that could be why this isn't working for me..
cffit
Expert
 
Posts: 338
Liked: 33 times
Joined: Fri Jan 20, 2012 2:36 pm
Full Name: Christensen Farms

Re: Can't get SureBackup SQL test to not fail

Veeam Logoby cffit » Mon Oct 24, 2016 3:29 pm

I feel kind of silly now. My fix was posted on here earlier.. by me :) I spent way too much time on this issue this time around.

Nobody from VEEAM really addressed my fix and questions from a year ago in this post. The SQL Checker script starts out with a SC command to enumerate the SQL instances on the remote SQL server. The problem is, to run a SC command against a remote server, the account being used has to be a local admin on that remote server. In our case, we run the script using a service account specifically created as a VEEAM backup service account. It is a domain account. It has admin rights on the VEEAM server and the VEEAM services run as it. BUT, it DOES NOT have admin rights on all our SQL servers. So even though you go through and give this service account public permissions on all the databases on the remote SQL server, it doesn't ever even get to the point of being able to check those databases. It fails immediately due to not having permissions to figure out what SQL instances are there because the SC command cannot be run with your VEEAM service account.

This is true whether you use the SQL authentication method or Windows authentication method in the manual. My only way to get this to work was to specify everything, and instead of just using "%log_path% %vm_ip% username password, I used "%log_path% %vm_ip%\mssqlserver" (I am using the VEEAM service account so no need to specify the username password as that's implied. If you were using a different account or SQL auth you would fill those in)

The other option to get this to all work is to either add your VEEAM service account (or whatever account you are using to run your script) to the local admin group of every SQL server you want to run the script against. Or if the account you are using is a member of domain admins it will work as well.

I think this is important info missing from the manual. It's possible I could be wrong and missing something here. But as far as I can tell, unless the account you are using to run the script is a local admin of the SQL server or a domain admin overall, the script will always fail trying to run the initial SC command to poll the SQL instances unless you manually specify the instance name, even if it is just a default instance.
cffit
Expert
 
Posts: 338
Liked: 33 times
Joined: Fri Jan 20, 2012 2:36 pm
Full Name: Christensen Farms

Re: Can't get SureBackup SQL test to not fail

Veeam Logoby cdlane » Wed Oct 26, 2016 5:34 am

Hi cffit,

I'm hoping you can shed some light on my scenario from your experience above.

VM is Server_A - 192.168.1.7 - SureBackup IP 192.168.254.7
Service account is DOMAIN\sa_veeam and is a member of the Server_A local admin group

If I sign on to the Veeam server as sa_veeam and run sc \\192.168.1.7 query, all the running services are returned which tells me I must (obviously) have permissions to the server to be SureBackup'ed - Server_A

If I run: -
cscript "C:\Program Files\Veeam\Backup and Replication\Backup\Veeam.Backup.SqlChecker.vbs" c:\logs 192.168.1.7

I receive "Exit code 0: Success" which tells me the SQL security settings must also be correct.

If I run a SureBackup job with %log_path% %vm_ip% 192.168.254.7 and credentials tab populated with either domain admin or sa_veeam

- OR -

If I run a SureBackup job with %log_path% %vm_ip% 192.168.254.7 <user> <password> (either domain admin or sa_veeam + password)

- OR -

I try the SC or csript command lines above whilst 192.168.254.7 is powered up within the Virtual_Lab...

I get Error 2; access is denied. Why? Whats different? All my Veeam services run as Local System and if I try and use sa_Veeam they complain and don't start correctly so I assume it has to be something to do with that?

Any insight you can offer would be welcome,

Best regards,

Chris
cdlane
Enthusiast
 
Posts: 43
Liked: 3 times
Joined: Sun Aug 09, 2015 5:48 pm
Full Name: Chris Lane

Re: Can't get SureBackup SQL test to not fail

Veeam Logoby cdlane » Wed Oct 26, 2016 1:38 pm

Correction: -

I try the SC or cscript command lines above whilst 192.168.254.7 is powered up within the Virtual_Lab... WORKS.

Its just the SureBackup invocations of SC fail
cdlane
Enthusiast
 
Posts: 43
Liked: 3 times
Joined: Sun Aug 09, 2015 5:48 pm
Full Name: Chris Lane

Re: Can't get SureBackup SQL test to not fail

Veeam Logoby cdlane » Thu Oct 27, 2016 11:34 am

Hi,

Firstly ID# 01959110 submitted requesting detailed instruction/settings/permissions required to get this script working.

Next, can somebody ask Vyacheslav Kuznetsov why the following is in Veeam.Backup.SQLChecker.vbs please?

Sub GetInstances(ByVal sServer, ByRef instanceList, ByRef excludeList)
Dim oShell, oExec, stdOut, sLine, sInstanceName, iPos, sCont, sCommand, bExcluded
iPos = InStr(sServer,"\")
'instance specified
If iPos>0 Then
sInstanceName = Mid(sServer, iPos+1)
If LCase(sInstanceName) = "mssqlserver" Then
sServer = Left(sServer, iPos-1)
End If


Why return only the server name and remove the instance name if it is MSSQLSERVER? At the moment I have to include an instance name (as per cffit's posts) otherwise it tries to run the SC command and always fails.

Best regards,

Chris
cdlane
Enthusiast
 
Posts: 43
Liked: 3 times
Joined: Sun Aug 09, 2015 5:48 pm
Full Name: Chris Lane

Re: Can't get SureBackup SQL test to not fail

Veeam Logoby overseer » Thu Oct 27, 2016 12:32 pm

Hi,

This code is added to distinguish situations when you would like to enumerate all instances and check them all (cscript Veeam.Backup.SqlChecker.vbs %server_ip%) and when you want to scan only default instance of specified server (cscript Veeam.Backup.SqlChecker.vbs %server_ip%\mssqlserver), in this case sc query will not be executed.

Sincerely,
Vyacheslav Kuznetsov
overseer
Veeam Software
 
Posts: 3
Liked: never
Joined: Tue Feb 05, 2013 1:46 pm
Full Name: Vyacheslav Kuznetsov

Re: Can't get SureBackup SQL test to not fail

Veeam Logoby cdlane » Thu Oct 27, 2016 12:56 pm

Hi Vyacheslav,

Many thanks for coming back so promptly.

My problem is when ever sc.exe is called from within a SureBackup job, it always fails. I've tried running the service as domain admin; it fails. I've tried adding the domain admin to the credentials tab; it fails. At the moment I have to use the "Use the following test script:" section and explicitly add the instance as a parameter...except it doesn't work when a database is using the default MSSQLSERVER.

Any guidance is much appreciated.

Best regards,

Chris
cdlane
Enthusiast
 
Posts: 43
Liked: 3 times
Joined: Sun Aug 09, 2015 5:48 pm
Full Name: Chris Lane

Re: Can't get SureBackup SQL test to not fail

Veeam Logoby overseer » Thu Oct 27, 2016 1:28 pm

Please try to add it the following way
cscript "C:\Program Files\Veeam\Backup and Replication\Backup\Veeam.Backup.SqlChecker.vbs" %log_path% %vm_ip%\mssqlserver

Does it work?
overseer
Veeam Software
 
Posts: 3
Liked: never
Joined: Tue Feb 05, 2013 1:46 pm
Full Name: Vyacheslav Kuznetsov

Re: Can't get SureBackup SQL test to not fail

Veeam Logoby cdlane » Thu Oct 27, 2016 3:00 pm

Afraid not, it still strips the mssqlserver.
cdlane
Enthusiast
 
Posts: 43
Liked: 3 times
Joined: Sun Aug 09, 2015 5:48 pm
Full Name: Chris Lane

Re: Can't get SureBackup SQL test to not fail

Veeam Logoby overseer » Thu Oct 27, 2016 7:56 pm

Hello Chris,

It will strip mssqlserver, because when conencting default SQL instance, connection string should contain just server name or ip rather than %server_name%\mssqlserver. In such case script will not perform SC query to enumerate instances, I've just verified it in my lab. You've opened a support case 01959110 regarding the issue, may I ask you to provide script logs for the SureBackup job with commandline set as cscript "C:\Program Files\Veeam\Backup and Replication\Backup\Veeam.Backup.SqlChecker.vbs" %log_path% %vm_ip%\mssqlserver
overseer
Veeam Software
 
Posts: 3
Liked: never
Joined: Tue Feb 05, 2013 1:46 pm
Full Name: Vyacheslav Kuznetsov

Re: Can't get SureBackup SQL test to not fail

Veeam Logoby cdlane » Fri Oct 28, 2016 8:30 am

HI Vyacheslav,

OK, I'm finally getting a handle on this, and improving my scripting skills as well...RPG/400 was so much easier than this modern scripting stuff though ;-)

Last night all my jobs completed successfully (including with %vm_ip%\MSSQLSERVER) with the following combination of settings (for anybody Googling this in the future): -

i) Veeam Back up Service running as Local System - no service accounts etc
ii) Credentials tab has the domain admin entered. I tried with the service account which is in the local admin group on all servers but isn't domain admin...it didn't work in that configuration
iii) All command lines manually entered explicitly list in the instance i.e. %log_path% %vm_ip%\MYDATABASE or %vm_ip%\MSSQLSERVER etc

So the only problem now is trying to use it without an instance explicitly defined or (better still) with the "Use predefined test script" radio button pressed. It always fails as below: -

EDIT: logs have been removed by moderator.

So, as per my comments further up the post; It's just the SureBackup invocations of SC that fail.

As an aside - stunning support and help from the people at Veeam as usual, many thanks.
cdlane
Enthusiast
 
Posts: 43
Liked: 3 times
Joined: Sun Aug 09, 2015 5:48 pm
Full Name: Chris Lane

PreviousNext

Return to VMware vSphere



Who is online

Users browsing this forum: No registered users and 11 guests