Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6042
  • Last Modified:

Backup Exec 2012 SQL instance - "Test failed"

I'm trying to backup a new SQL server using Backup Exec 2012. My knowledge of SQL is very limited. This SQL server was set up by consultants who are implementing some product repository software called EpaCube for us. They've set up 3 SQL instances on the SQL 2008 R2 server.

I'm having issues with the test credentials portion for this server's backup. Credentials for normal data is successful, but having issues with the SQL credentials.

Every time i try to test any logins, the Microsoft SQL server comes back with success but all 3 SQL instances fail with a message "Test failed, Test recommended" in the Credential Status column of the job.

I've tested multiple logins for the SQL portion of the backup job.  For the SQL server, I've used various logins, from the SA account, to the domain account used by the consultants to set up the software

I also tried the domain admin account. I logged into SQL as SA, added the domain admin account to the security list, with the sysadmin role (as well as public).

Am I missing some role, or some kind of hidden feature in SQL that I'm not aware of?

Any ideas?
sql-backup.JPG
0
HornAlum
Asked:
HornAlum
  • 6
  • 5
2 Solutions
 
prashant9885Commented:
Have you tried accessing the instances with System Logon Account?  Is the long account part of domain admins?

Instances in mix mode authentication ?
0
 
HornAlumAuthor Commented:
the default login account is the primary administrator account for the domain, which doesn't work.

I'm not sure what you mean by mix mode authentication ... I'm not terribly familiar with SQL. I know it accepts both windows and sql server logins
0
 
Marten RuneCommented:
In SQL 2008 R2, builtin administrators are not automatically SA.
Do you have the SA pwd.

If so I would suggest you try adding the desired account to all of the instances, and granting it the db_backupoperator role in the msdb database.

But a quick test would be to grant this windows account sysadmin rights. If this works start scaling down the rights, bye dropping the user, and recreating it with the db_backupoperator role rights.

Some code to help you get starting:
USE MASTER
GO
CREATE LOGIN [DOMAIN\USERACCOUNT] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
EXEC master..sp_addsrvrolemember @loginame = N'DOMAIN\USERACCOUNT', @rolename = N'sysadmin'
GO


USE [msdb]
GO
CREATE USER [DOMAIN\USERACCOUNT] FOR LOGIN [DOMAIN\USERACCOUNT] WITH DEFAULT_SCHEMA=[DBO]
GO
USE [msdb]
GO
EXEC sp_addrolemember N'db_backupoperator', N'DOMAIN\USERACCOUNT'
GO


--REVOKE Sysadmin
USE MASTER
GO
EXEC master..sp_addsrvrolemember @loginame = N'DOMAIN\USERACCOUNT', @rolename = N'sysadmin'
GO

Open in new window


Regards Marten
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
HornAlumAuthor Commented:
yep, i have the SA account password. I added the domain account under Security->Logins in the SQL Management Studio. It has sysadmin rights, but like i said, both the SA account and the account i added both fail in backup exec when it comes to rights to the instances.

How do i add the login account to each of the instances? I may not be following the difference between using the Management Studio and creating a login there, vs creating a login for the three different instances.
0
 
Marten RuneCommented:
First alter Domain\useraccount to match your environment,
Then connect a query to each of the instances.
Run the code, this code grants the rights per instance i e the instance the query is connected to.
You can do this in SSMS by pressing:
File\New\Database Engine Query
Now fill in desired connection, and a Sysadmin account, or run with windows credentials that has these rights on the sql server, if so choose Windows Authentication.
This step File\New... is repeted for the other two instances you want to grant access to.

Now use the RIGHT-CLICK on SSMS, and start the program as another user, in your case what you replaced the Domain\useraccount with! Use windows authentication and connect to each of the instances. Now you have confirmed that this works.

From the Backup Exec server, log on as the domain\useraccount, and create a ODBC source. just click start button and type ODBC
RIGHT-CLICK Data Sources (ODBC) and run as administrator
Create a System DSN, Add.. button, choose SQL Server the highest version you have availible. Fill in Name, Description and the instance path as Server. Use With Integrated Windows authentication and click next, and next again till you see the button Test Data Source... Klick this button, and check that connection is successful. Now cancel out!!! So you dont create a ODBC datasource. Now you have validated the right account, from the backup exec server against the sql server. Repeat for each instance.

Regards Marten
0
 
HornAlumAuthor Commented:
the code you're referring to is the code you attached in ID: 38367787?

i'll try this and let you know what happens.
0
 
Marten RuneCommented:
Yes the code from line 1 to line 16, not the revokation part!

Regards Marten
0
 
HornAlumAuthor Commented:
where/how do i specify the actual instance that i'm running the code for? is it in the code, or do i specify in the connect engine?

when i go to the connect to database engine, the servername is just ".", for the server that i'm logged into. you may remember from the attached screenshot, the instance names are MSSQLSERVER, EPATEST and EPATRAIN
0
 
HornAlumAuthor Commented:
Nevermind, i didn't realize i had to use the Servername\Instancename when using the database engine login screen, and that the MSSQLSERVER was the default instance name i connect to by using a "." for the server or the servername itself

Using the domain account we use to back up everything else in backup exec, I ran the code just like you said, then did a "Test Data Source" using the Data Sources (ODBC). I logged into the SQL instances just like you said and saw the login sitting there, with public and sysadmin roles.

I went back to backup exec and I am still getting "Test failed, Test recommended".

any other ideas?
0
 
Marten RuneCommented:
Now you KNOW that rights are there.
Id look into backup exec configuration next!
Not my cup of tea since I do backups to files, and we have a storage team responsible for the backups of these fileareas.
0
 
HornAlumAuthor Commented:
Finally figured it out. I logged onto the SQL server and looked at the EVENT LOGS. There were some Bakcup Exec 57860 errors in the event log that looked something like this

An error occurred while attempting to log in to the following server: "domain.com\MSSQLSERVER".
 SQL error number: "4818".
 SQL error message: "Login failed for user 'domain\NTSERVICE'.
".

I believe in the past, backup exec was set up to log on as that account, maybe in the services window. It's not set up that way any longer, but that made me think that even though i was using a specific domain admin account for the backup job, it was still using this login as the login account. the other SQL servers already had this account set up.

So, i added this login account as well to the instances as well, using the script you gave me. now all tests ok!

thanks!!
0
 
Marten RuneCommented:
Glad to see it resolved

Regards Marten
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now