Host-based backup of VMware vSphere VMs.
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

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

Post by Vitaliy S. »

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.
bg.ranken
Expert
Posts: 121
Liked: 21 times
Joined: Feb 18, 2015 8:13 pm
Full Name: Randall Kender
Contact:

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

Post by bg.ranken »

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.
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

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

Post by Vitaliy S. »

Yes, we've spotted it too, passwords are not exposed in Veeam Availability Orchestrator log files.
cffit
Veteran
Posts: 338
Liked: 35 times
Joined: Jan 20, 2012 2:36 pm
Full Name: Christensen Farms
Contact:

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

Post by cffit »

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
Veteran
Posts: 338
Liked: 35 times
Joined: Jan 20, 2012 2:36 pm
Full Name: Christensen Farms
Contact:

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

Post by cffit »

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
Veteran
Posts: 338
Liked: 35 times
Joined: Jan 20, 2012 2:36 pm
Full Name: Christensen Farms
Contact:

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

Post by cffit » 1 person likes this post

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.
cdlane
Enthusiast
Posts: 53
Liked: 3 times
Joined: Aug 09, 2015 5:48 pm
Full Name: Chris Lane
Contact:

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

Post by cdlane »

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: 53
Liked: 3 times
Joined: Aug 09, 2015 5:48 pm
Full Name: Chris Lane
Contact:

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

Post by cdlane »

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: 53
Liked: 3 times
Joined: Aug 09, 2015 5:48 pm
Full Name: Chris Lane
Contact:

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

Post by cdlane »

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
overseer
Veeam Software
Posts: 3
Liked: never
Joined: Feb 05, 2013 1:46 pm
Full Name: Vyacheslav Kuznetsov
Contact:

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

Post by overseer »

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
cdlane
Enthusiast
Posts: 53
Liked: 3 times
Joined: Aug 09, 2015 5:48 pm
Full Name: Chris Lane
Contact:

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

Post by cdlane »

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
overseer
Veeam Software
Posts: 3
Liked: never
Joined: Feb 05, 2013 1:46 pm
Full Name: Vyacheslav Kuznetsov
Contact:

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

Post by overseer »

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?
cdlane
Enthusiast
Posts: 53
Liked: 3 times
Joined: Aug 09, 2015 5:48 pm
Full Name: Chris Lane
Contact:

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

Post by cdlane »

Afraid not, it still strips the mssqlserver.
overseer
Veeam Software
Posts: 3
Liked: never
Joined: Feb 05, 2013 1:46 pm
Full Name: Vyacheslav Kuznetsov
Contact:

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

Post by overseer »

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
cdlane
Enthusiast
Posts: 53
Liked: 3 times
Joined: Aug 09, 2015 5:48 pm
Full Name: Chris Lane
Contact:

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

Post by cdlane »

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.
dyna
Novice
Posts: 5
Liked: never
Joined: Feb 02, 2017 3:03 pm
Contact:

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

Post by dyna »

Same issues here.

Only way i got it to work is by using SQL auth without enumeration (by specifing the instance name)

I was trying to get to the the bottom of it but frankly jus gave up.

How is the enumeration suppost to work? Is sudo/runas being used for this else i can't see how it should work.

It looks like custom domain login just isn't possible. You either specifiy credentials and use SQL login, or you use domain login and it will use the the credentials from the current user.

When i'm logged in as admin and try to run the script it works fine without credentials (using my current admin user credentials), but when i manually specifiy the same admin credentials it fails and the error on the sql server will be "Login failed for user 'DOMAIN\adminuser'. Reason: Could not find a login matching the name provided." because it is looking for an SQL login user with that name.

However to be able to use SC for enumration, the user must be member of the local admin group. But an SQL user can't be part of a local system group.

So the only reason i could see this work if it's runs sudo'ed as the credential user and then don't pass credentials on the sql connecting. However that was my starting position and couldn't get that too work, maybe because of the virtual lab seperated network and not being able to connect a DC?
dyna
Novice
Posts: 5
Liked: never
Joined: Feb 02, 2017 3:03 pm
Contact:

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

Post by dyna »

I guess running the veeam service(s) as domain user and giving this user local admin on the sql server should work as well, this is probably how other people got it running. However that's means 0 flexibility because it always has to use that account for domain logins, and would make the credentials per VM option only work for sql logins.

The other option is SQL auth with no enumeration, to clarify: %log_path% %vm_ip%\mssqlserver sqluser password
dyna
Novice
Posts: 5
Liked: never
Joined: Feb 02, 2017 3:03 pm
Contact:

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

Post by dyna »

Small update. I was banging my head against this again and then i found out that enumeration with sql credentials can work but only if an admin is logged in, which threw me off in tests at first.

As in:
-SB boots a VM to logon screen, enumeration fails
-Log into the VM using console, enumeration works
-Log out of the VM, enumeration fails again

I think it's just impossible to actually enumerate during the SB process (unless it auto logs in maybe) without changing the service users (either sql or veeam).

Anyway this whole process NEEDS to be looked at as it's really very amateuristic and imo definitely not Veeam worthy. You are probably better off removing to not throw off new users. (The current few who did got it working won't like that tho). Better drop it from the UI and make some other custom hooking option or something.
getzjd
Enthusiast
Posts: 25
Liked: 3 times
Joined: Mar 12, 2012 3:10 am
Full Name: Jay
Location: Cincinnati, OH
Contact:

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

Post by getzjd »

Still trying to get a simple authentication to work. Case# 02419825
Does it have to be a SQL user/pass that is being passed though on the script or can it be a domain account with public permissions ?
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

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

Post by foggy »

Adecus
Lurker
Posts: 1
Liked: never
Joined: Oct 23, 2018 1:13 pm
Full Name: Adam Walsh
Contact:

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

Post by Adecus »

SQL Server Browser needed to be enabled/set as Automatic for us. This was the final missing piece of the puzzle after trying everything mentioned here.
MPlesnerJ
Veeam Software
Posts: 153
Liked: 44 times
Joined: Apr 01, 2014 12:30 pm
Full Name: Martin Plesner-Jacobsen
Contact:

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

Post by MPlesnerJ »

Hi All,
VBR server is Windows server 2019 with default settings. SQL server in Veeam Lab (Surebackup Lab) is running SQL server 2019 on Windows server 2019 with default settings.
SureBackup roles is running under my Domain\administrator account.
UAC on VBR and SQL server set to default.

I found that my SQL script was failing with SC error "[SC] OpenSCManager FAILED 1722" I disabled the firewall on Public and Domain profile in the SQL server, and that error was fixed.
Next
I got the "[SC] OpenSCManager FAILED 5" and I found that I forgot to add the domain controller to the SQL servers DNS list. That being the RIGHT DNS that also works in the Lab, because it was pointing to the physical DC only.

Let me know if this helps you in debugging ;-)
williamlaw
Influencer
Posts: 11
Liked: 2 times
Joined: Jul 09, 2018 2:37 am
Full Name: W.
Contact:

[MERGED] SureBackup - SQL ver

Post by williamlaw »

Hi All,

I have created a SureBackup job to verify SQL server, but encountered failure for SQL verification with below error.

Info [SC] OpenSCManager FAILED 5:Access is denied.

How can I solve it ?

Thanks
HannesK
Product Manager
Posts: 14287
Liked: 2877 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

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

Post by HannesK »

Hello,
did you already try the answers above?

Best regards,
Hannes
WinstonWolf
Veteran
Posts: 284
Liked: 11 times
Joined: Jan 06, 2011 8:33 am
Contact:

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

Post by WinstonWolf » 1 person likes this post

Hello ,
For all which have Problems with the SQL Skript in the Sure Backup Job here an Solution which helps on our Site .

The SQL Checker vbs Script trys to connect to the SQL Server via IP Address .
BUT THIS IS NOT POSSIBLE WHEN THE SQL BROWSER SERVICE IS DISABLED .

Please check if the SQL Browser Servis is set to automatically Sartup and have the Status "enabled"

Hope the helps a little bit .

Michael
nevram
Novice
Posts: 8
Liked: never
Joined: Sep 20, 2021 3:41 pm
Full Name: Matthew Marven
Contact:

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

Post by nevram »

cffit wrote: 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.
Hello, thanks for this post! After over a month of troubleshooting this issue with Veeam Support (#02709929), I have seen a successful SQL SureBackup job in my Veeam console thanks to you.

My problem is that to get this working, I have had to add the arguments including the SA credentials in clear text. I need to figure out how to get this working using Windows Authentication when my VBR server is outside the domain (without specifying credentials in clear text?).

I agree that the details you have provided make for vital information missing from Veeam's Documentation, and given this topic was raised 8 years ago, I am a little surprised!
Post Reply

Who is online

Users browsing this forum: No registered users and 93 guests