Jump to content
kgleason

SQl Agent Authentication

Recommended Posts

Hello all,

Here is my situation ...

 

1. Windows 2003 Server 32 bit running RetroSpect MultiServer backups & 1 SQL Agent License. RetroSpect version is 7.5.330.

2. SQL Server 2005 64 bit running on WIndows 2003 (default instance -- no cluster).

 

When I attempt to assign the SQL Agent license to the SQL Instance on that client, Retrospect pops up with an authentication dialog box. I have tried everything, but it always fails. I have an RBU user as outlined in the Users guide / Help which does not work. I have also tried a domain administrator, a local administrator, sa, another SQL user that has dbo rights on the database. None of them work.

 

All I can get from RetroSpect support is "it should be working.". If anyone has any ideas about what it could be, I would really appreciate the help.

Share this post


Link to post
Share on other sites

Hi Plagued -

 

This is a known issue which Technical Support should be aware of. I would recommend calling a second time.

 

Thanks

Amy

Share this post


Link to post
Share on other sites

I upgraded to 7.5.370 on the server (clients, even after upgrade still showing 117 version). I can log into MS SQL 05 server at all. NONE of the account that work anywhere else work with Retrsopect.

Share this post


Link to post
Share on other sites

Try relicensing SQL under Configure > Volumes. Release the license by right-clicking on the SQL Server.

 

If the problem continues try forgetting the client and re-adding it again under Configure > Clients.

Share this post


Link to post
Share on other sites

I'm having the same problem. The server is running 7.5.370 and releasing the license / forgetting and re-adding the server does not resolve the issue.

 

I've also tried creating a local account with full admin rights, as well as a straight SQL account with DBO rights. NOTHING seems to work here.

Share this post


Link to post
Share on other sites

Did anyone ever get any of this stuff resolved? If not, I am gonna have to drop retrospect and move to something else. Seems ridiculous that this does not work ....

Share this post


Link to post
Share on other sites

If forgetting and re-adding the client does not work, try with new configuration files.

 

-Rename the C:\Documents and Settings\All Users\Application Data\Retrospect folder

-Launch Retrospect and enter your license codes

-Login/license SQL

 

This problem cannot be reproduced in the lab - all authentication methods succeed both locally and via the client. You'll need to provide more information about your environment.

 

Any non-default options selected on install?

Are all the SQL Services running under the same Administrator account?

Any non-default security options configured after install?

Any other changes you can think of which may impact security?

Share this post


Link to post
Share on other sites

Also check your SQL Log File Viewer.

 

- Uncheck SQL Server

- Check Windows NT

- Expand Windows NT and un-check Application, Internet Explorer, System

- Attempt to authenticate through Retrospect

- Refresh the SQL log and check for Security errors

Share this post


Link to post
Share on other sites

We haven't really done anything fancy with SQL Server or Retrospect. Stock install of SQL Server on 2 different servers. One server is a Dell 2850 (32bit) running Windows 2003 STandard. The other server is a Dell 2950 (64bit) running Windows 2003 Standard for 64 bit.

 

The 32bit SQL server is letting me authenticate. However, the 64 bit one is not. All services are running with default credentials, and SQL has NT and SQL authentication enabled. Both SQL Servers are only running a default instance.

 

I have tried authenticating as the following users:

 

1. Domain Administrator

2. Active Directory user who is also a domain administrator.

3. An RBU account that I created according to the specs in the help.

4. sa

5. another sql user who has DBO on all databases

 

None of them work on the 64 bit SQL server. (The Domain Administrator works on the 32 bit SQL server).

 

I have tried forgetting clients, forgetting licenses, just about everything you have mentioned. I am not going to remove / rename the retrospect config because there is too much stuff configured on that thing, and the last time I tried renaming it (based on advice from Tech support), I had to reconfigure everything from scratch, and it obviously didn't work anyhow. Sorry, not gonna do it.

 

I did have a look at the logs. I cleared the Security log, and then attempted to make Retrospect connect to the database, and here is what I am seeing:

 

Code:


 Date,Source,Severity,Message,Category,Event,User,Computer

 

07/17/2007 12:59:11,Security,Success Audit,User Logoff:<nl/><nl/> User Name: administrator<nl/><nl/> Domain: RMGCOM<nl/><nl/> Logon ID: (0x2<c/>0x115F4277)<nl/><nl/> Logon Type: 5,Logon/Logoff,538,RMGCOM\administrator,DB1

 

07/17/2007 12:59:04,Security,Success Audit,User Logoff:<nl/><nl/> User Name: Administrator<nl/><nl/> Domain: RMGCOM<nl/><nl/> Logon ID: (0x2<c/>0x115F78EB)<nl/><nl/> Logon Type: 3,Logon/Logoff,538,RMGCOM\administrator,DB1

 

07/17/2007 12:59:04,Security,Success Audit,Successful Network Logon:<nl/><nl/> User Name: Administrator<nl/><nl/> Domain: RMGCOM<nl/><nl/> Logon ID: (0x2<c/>0x115F78EB)<nl/><nl/> Logon Type: 3<nl/><nl/> Logon Process: Kerberos<nl/><nl/> Authentication Package: Kerberos<nl/><nl/> Workstation Name: <nl/><nl/> Logon GUID: {0c87f0e4-59a0-ae31-7de5-f98c8195d0f3}<nl/><nl/> Caller User Name: -<nl/><nl/> Caller Domain: -<nl/><nl/> Caller Logon ID: -<nl/><nl/> Caller Process ID: -<nl/><nl/> Transited Services: -<nl/><nl/> Source Network Address: -<nl/><nl/> Source Port: -,Logon/Logoff,540,RMGCOM\administrator,DB1

 

07/17/2007 12:59:04,Security,Success Audit,Special privileges assigned to new logon:<nl/><nl/> User Name: Administrator<nl/><nl/> Domain: RMGCOM<nl/><nl/> Logon ID: (0x2<c/>0x115F78EB)<nl/><nl/> Privileges: SeAssignPrimaryTokenPrivilege<nl/> SeSecurityPrivilege<nl/> SeBackupPrivilege<nl/> SeRestorePrivilege<nl/> SeTakeOwnershipPrivilege<nl/> SeDebugPrivilege<nl/> SeSystemEnvironmentPrivilege<nl/> SeLoadDriverPrivilege<nl/> SeImpersonatePrivilege,Logon/Logoff,576,RMGCOM\administrator,DB1

 

07/17/2007 12:59:04,Security,Success Audit,User Logoff:<nl/><nl/> User Name: Administrator<nl/><nl/> Domain: RMGCOM<nl/><nl/> Logon ID: (0x2<c/>0x115F77E7)<nl/><nl/> Logon Type: 3,Logon/Logoff,538,RMGCOM\administrator,DB1

 

07/17/2007 12:59:04,Security,Success Audit,Successful Network Logon:<nl/><nl/> User Name: Administrator<nl/><nl/> Domain: RMGCOM<nl/><nl/> Logon ID: (0x2<c/>0x115F77E7)<nl/><nl/> Logon Type: 3<nl/><nl/> Logon Process: Kerberos<nl/><nl/> Authentication Package: Kerberos<nl/><nl/> Workstation Name: <nl/><nl/> Logon GUID: {0c87f0e4-59a0-ae31-7de5-f98c8195d0f3}<nl/><nl/> Caller User Name: -<nl/><nl/> Caller Domain: -<nl/><nl/> Caller Logon ID: -<nl/><nl/> Caller Process ID: -<nl/><nl/> Transited Services: -<nl/><nl/> Source Network Address: -<nl/><nl/> Source Port: -,Logon/Logoff,540,RMGCOM\administrator,DB1

 

07/17/2007 12:59:04,Security,Success Audit,Special privileges assigned to new logon:<nl/><nl/> User Name: Administrator<nl/><nl/> Domain: RMGCOM<nl/><nl/> Logon ID: (0x2<c/>0x115F77E7)<nl/><nl/> Privileges: SeAssignPrimaryTokenPrivilege<nl/> SeSecurityPrivilege<nl/> SeBackupPrivilege<nl/> SeRestorePrivilege<nl/> SeTakeOwnershipPrivilege<nl/> SeDebugPrivilege<nl/> SeSystemEnvironmentPrivilege<nl/> SeLoadDriverPrivilege<nl/> SeImpersonatePrivilege,Logon/Logoff,576,RMGCOM\administrator,DB1

 

07/17/2007 12:59:04,Security,Success Audit,User Logoff:<nl/><nl/> User Name: Administrator<nl/><nl/> Domain: RMGCOM<nl/><nl/> Logon ID: (0x2<c/>0x115F76AD)<nl/><nl/> Logon Type: 3,Logon/Logoff,538,RMGCOM\administrator,DB1

 

07/17/2007 12:59:04,Security,Success Audit,Successful Network Logon:<nl/><nl/> User Name: Administrator<nl/><nl/> Domain: RMGCOM<nl/><nl/> Logon ID: (0x2<c/>0x115F76AD)<nl/><nl/> Logon Type: 3<nl/><nl/> Logon Process: Kerberos<nl/><nl/> Authentication Package: Kerberos<nl/><nl/> Workstation Name: <nl/><nl/> Logon GUID: {0c87f0e4-59a0-ae31-7de5-f98c8195d0f3}<nl/><nl/> Caller User Name: -<nl/><nl/> Caller Domain: -<nl/><nl/> Caller Logon ID: -<nl/><nl/> Caller Process ID: -<nl/><nl/> Transited Services: -<nl/><nl/> Source Network Address: -<nl/><nl/> Source Port: -,Logon/Logoff,540,RMGCOM\administrator,DB1

 

07/17/2007 12:59:04,Security,Success Audit,Special privileges assigned to new logon:<nl/><nl/> User Name: Administrator<nl/><nl/> Domain: RMGCOM<nl/><nl/> Logon ID: (0x2<c/>0x115F76AD)<nl/><nl/> Privileges: SeAssignPrimaryTokenPrivilege<nl/> SeSecurityPrivilege<nl/> SeBackupPrivilege<nl/> SeRestorePrivilege<nl/> SeTakeOwnershipPrivilege<nl/> SeDebugPrivilege<nl/> SeSystemEnvironmentPrivilege<nl/> SeLoadDriverPrivilege<nl/> SeImpersonatePrivilege,Logon/Logoff,576,RMGCOM\administrator,DB1

 

07/17/2007 12:59:04,Security,Success Audit,Successful Network Logon:<nl/><nl/> User Name: Administrator<nl/><nl/> Domain: RMGCOM<nl/><nl/> Logon ID: (0x2<c/>0x115F7575)<nl/><nl/> Logon Type: 3<nl/><nl/> Logon Process: Kerberos<nl/><nl/> Authentication Package: Kerberos<nl/><nl/> Workstation Name: <nl/><nl/> Logon GUID: {0c87f0e4-59a0-ae31-7de5-f98c8195d0f3}<nl/><nl/> Caller User Name: -<nl/><nl/> Caller Domain: -<nl/><nl/> Caller Logon ID: -<nl/><nl/> Caller Process ID: -<nl/><nl/> Transited Services: -<nl/><nl/> Source Network Address: -<nl/><nl/> Source Port: -,Logon/Logoff,540,RMGCOM\administrator,DB1

 

07/17/2007 12:59:04,Security,Success Audit,Special privileges assigned to new logon:<nl/><nl/> User Name: Administrator<nl/><nl/> Domain: RMGCOM<nl/><nl/> Logon ID: (0x2<c/>0x115F7575)<nl/><nl/> Privileges: SeAssignPrimaryTokenPrivilege<nl/> SeSecurityPrivilege<nl/> SeBackupPrivilege<nl/> SeRestorePrivilege<nl/> SeTakeOwnershipPrivilege<nl/> SeDebugPrivilege<nl/> SeSystemEnvironmentPrivilege<nl/> SeLoadDriverPrivilege<nl/> SeImpersonatePrivilege,Logon/Logoff,576,RMGCOM\administrator,DB1

 

07/17/2007 12:59:03,Security,Success Audit,Special privileges assigned to new logon:<nl/><nl/> User Name: <nl/><nl/> Domain: <nl/><nl/> Logon ID: (0x2<c/>0x115F4277)<nl/><nl/> Privileges: SeAssignPrimaryTokenPrivilege<nl/> SeImpersonatePrivilege<nl/> SeSecurityPrivilege<nl/> SeBackupPrivilege<nl/> SeRestorePrivilege<nl/> SeTakeOwnershipPrivilege<nl/> SeDebugPrivilege<nl/> SeSystemEnvironmentPrivilege<nl/> SeLoadDriverPrivilege,Logon/Logoff,576,RMGCOM\administrator,DB1

 

07/17/2007 12:59:03,Security,Success Audit,Successful Logon:<nl/><nl/> User Name: administrator<nl/><nl/> Domain: RMGCOM<nl/><nl/> Logon ID: (0x2<c/>0x115F4277)<nl/><nl/> Logon Type: 5<nl/><nl/> Logon Process: Advapi <nl/><nl/> Authentication Package: Negotiate<nl/><nl/> Workstation Name: DB1<nl/><nl/> Logon GUID: {ba5a6fc9-8d55-cbde-e357-de383140fb15}<nl/><nl/> Caller User Name: DB1$<nl/><nl/> Caller Domain: RMGCOM<nl/><nl/> Caller Logon ID: (0x0<c/>0x3E7)<nl/><nl/> Caller Process ID: 5704<nl/><nl/> Transited Services: -<nl/><nl/> Source Network Address: -<nl/><nl/> Source Port: -,Logon/Logoff,528,RMGCOM\administrator,DB1

 

07/17/2007 12:59:03,Security,Success Audit,Logon attempt using explicit credentials:<nl/><nl/>Logged on user:<nl/><nl/> User Name: DB1$<nl/><nl/> Domain: RMGCOM<nl/><nl/> Logon ID: (0x0<c/>0x3E7)<nl/><nl/> Logon GUID: {343938d7-190f-67ad-00dc-1d99b5eeeccc}<nl/><nl/>User whose credentials were used:<nl/><nl/> Target User Name: administrator<nl/><nl/> Target Domain: RMGCOM<nl/><nl/> Target Logon GUID: {ba5a6fc9-8d55-cbde-e357-de383140fb15}<nl/><nl/><nl/>Target Server Name: localhost<nl/><nl/>Target Server Info: localhost<nl/><nl/>Caller Process ID: 5704<nl/><nl/>Source Network Address: -<nl/><nl/>Source Port: -,Logon/Logoff,552,NT AUTHORITY\SYSTEM,DB1

 

07/17/2007 12:58:37,Security,Success Audit,The audit log was cleared<nl/><nl/><nl/> Primary User Name: SYSTEM<nl/><nl/> Primary Domain: NT AUTHORITY<nl/><nl/> Primary Logon ID: (0x0<c/>0x3E7)<nl/><nl/> Client User Name: administrator<nl/><nl/> Client Domain: RMGCOM<nl/><nl/> Client Logon ID: (0x0<c/>0x1DA6D),System Event,517,NT AUTHORITY\SYSTEM,DB1

 


Share this post


Link to post
Share on other sites

Was this SQL Server ever renamed by chance? I was having this same issue, and with a hint from technical support that it was using the old server name to authenticate to the SQL database, I found the solution.

 

Change the following keys to match the new server name:

 

HKLM\Software\Microsoft\Microsoft SQL Server\90\Machines\OriginalMachineName

HKLM\Software\WOW6432Node\Microsoft\Microsoft SQL Server\90\Machines\OriginalMachineName

 

(The latter key will only be present on 64-bit machines).

 

Once you've done that, restart the SQL services, the Retrospect client service, forget and re-add the client to the Retrospect server, then try and setup the volumes for the SQL Server databases. When I did all of this, it suddenly worked like a champ! I have seen no issues with changing those registry keys in any other app that uses the SQL Server, including several LOB and web applications.

 

Hope that helps. Now that I've solved my SQL issues, looks like I'll be joining the Retrospect family. I'm really in love with the way this product works!

Share this post


Link to post
Share on other sites

Hi,

 

Thanks for sharing the info, the registry trick is useful and it worked very well on 32bit version of SQL server.

 

However, another host which has 64bit SQL server is still refusing retrospect logins and the registry keys have correct values already. I tried every possible solution given in this topic - no good, il always fails, but win event security log on sql server host shows "login success..", any idea what's wrong ?

 

Regards.

 

Share this post


Link to post
Share on other sites

Evaldas, you are totally new to this thread so it is important to describe the exact problem you are seeing and the configuration.

 

What version of Retrospect are you using?

What operating system is Retrospect on?

 

Is the SQL Server a client or is it local to Retrospect?

 

Did you configure an RBU account? What groups is it a member of?

 

Did you configure the Automatic Login in Configure>Volumes?

 

Which Authentication Method are you using?

 

The current Retrospect beta has some SQL bug fixes. You should try with that too.

Share this post


Link to post
Share on other sites

Hi, sorry, below are the details:

 

Retrospect multi-server v7.5.508 running on windows xp x64.

 

SQL server is a client (MS-SQL 2005 x64), running on windows server 2003 R2 Enterprise x64 SP1.

 

No, I'm not using RBU, the client is on a different network and not part of the retrospect server domain.

 

Configure->Volumes->BackupClients->Client1->SQLserver thats where a window pops up for authentication to SQL server, and I use "Domain authentication". The client with SQL server is not part of any domain, so in "Domain:" part I use client netbios name, and then administrator/pass for the rest.

 

I have another client, where it works, and the only diffs between the two are:

 

- OS, OKclient has win 2003 R2 Std x64 SP2

BADclient has win 2003 R2 Enterprise x64 SP1

 

- installed software list on OKclient has "MSSQL server 2005 Backward compatibility" which is missing on BADclient.

 

I'll check if there are any updates for Enterprise x64 SP1, and add missing software component on BADclient, and then do some more testing, thanks.

 

Share this post


Link to post
Share on other sites
Configure->Volumes->BackupClients->Client1->SQLserver thats where a window pops up for authentication to SQL server, and I use "Domain authentication". The client with SQL server is not part of any domain, so in "Domain:" part I use client netbios name, and then administrator/pass for the rest.

 

This will probably not work. If you do use Domain Authentication, the account you select MUST be a member of:

 

Domain Users

Domain Administrators

Backup Operators

maybe local Administrator

No other groups.

 

I would suggest use SQL Authentication instead of Domain Authentication.

 

Try installing a copy of Retrospect in the same domain server as the SQL client, configure the RBU and see if you can connect.

 

You should try the 7.5 beta in the beta section of the forum.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×