Archive | Troubleshooting RSS feed for this section

Manual Failover error on SQL Server 2014SP1 AlwaysOn

21 Jan

I implemented AlwaysOn Availability groups solution on SQL Server 2014 SP1.

During the testing of a manual failover (via the GUI), I encountered the following error:

Availability-group DDL operations are permitted only when you are using the master database.  Run the USE MASTER command, and retry your availability-group DDL command. (Microsoft SQL Server, Error: 35208)

I was puzzled by the error, as it manifested itself just after we installed the latest  Cumulative update package 4 (CU4) for SQL Server 2014 Service Pack 1.
For a few moments I though that I have encountered a new bug.

I tried restarting the machine (the whole implementation wasn’t in production just yet) and I could perform one manual failover, but upon attempting another manual failover we got that error message again.

I tried performing a manual failover via Transact-SQL:

ALTER AVAILABILITY GROUP <AG01> FAILOVER;

the failover went fine with no issues.

This led me to suspect that it’s a login/user related issue.

I logged out and connected with SA account. I tried performing  a manual failover via the GUI, and it finished successfully.
This confirmed me it was a login/user and not a GUI bug.

I checked the properties of my login (it was a group for the SQL admins) and saw that the default database wasn’t master, but a user database that was recently created by the application.
Once the default database was changed back to master, we had no issues with the manual failovering.

Hope it’ll help somebody in case of need.
I haven’t found many articles regarding this issue online.

Advertisements

Running a SSIS package via SQL Agent job with a Proxy User

23 Sep

I recently needed to create a SQL Agent job that runs a SSIS package on SQL Server 2012.

I followed the steps that are detailed in the below link, to be able to run a SSIS Package from SQL Server Agent Using a Proxy Account:
https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

However, once I finished the steps and tried running the job, it kept failing with the error:

Unable to start execution of step 1 (reason: Error authenticating proxy *****\******, system error: Logon failure: unknown user name or bad password.).  The step failed.

I was sure that password of the Proxy Account was correct, and I was quite puzzled with the issue.
I even tried changing the Proxy Account to another sysadmin account but no change.

Solution:

Check the SQL Server Agent service account in SQL Server Configuration Manager. Make sure the service account is configured with the format of DomainName\UserName and not in the format of UserName@DomainName.com.

Once I change the Service account configuration, the job ran with no issues.

Errors in SQLAGENT.OUT log – Unable to open or read TSX cache file

14 Mar

Hello,

I’ve recently implemented a MSX/TSX jobs environment in my organization.

I’ll blog about it at a different time.

Source: Microsoft.com

Source: Microsoft.com

PROBLEM:

Everything seams to work fine with no issues, except for a strange error message in the SQLAGENT.OUT log:

[419] Unable to open or read TSX cache file ‘E:\MSSQL10.DB\MSSQL\JOBS\TSXCACHE.BIN’ (win32 error code: 2)

Despite the error above, there’s no issues with the implementation – all jobs are working as expectantly.

SOLUTION:

The solution to the problem is to give the user account of the SQL Server Agent modify permissions to the directory:
<SQL SERVER DIRECTORY>\<MSSQL SID>\MSSQL\JOBS

After giving the permissions, you’ll see immediately that a new file called TSXCACHE.BIN will be created in the directory.


Enjoy,
Roni Vered Adar.