Friday, October 24, 2014

"An attempt was made to access a socket in a way forbidden by its access permissions" - Database Mail Failure

Database Mail fails with the following error:

"The mail could not be sent to the recipients because of the mail server failure. Exception Message: Could not connect to mail server. (An attempt was made to access a socket in a way forbidden by its access permissions (IP))."

Issue:

A recent McAfee Anti-Virus update began to block programs from sending mail unless they appear on a list of excluded programs. SQL Server Database Mail does not appear on that list by default.

You may see an error in the McAfee logs such as:

Date_Time - (Path)\MSSQL\Binn\DatabaseMail.exe Anti-virus Standard Protection:Prevent mass mailing worms from sending mail (IP)

Fix:

  • Go to the Access Protection Properties in the VirusScan console.
  • Select Anti-Virus Standard Protection.
  • Highlight mass mailing worms, and edit the property. 
  • Add DatabaseMail.exe to the list of excluded programs.

This was seen on SQL Server 2012, though it is relevant to all SQL Server versions using Database Mail and has to do with the McAfee configuration. 

Wednesday, January 22, 2014

Fix: "The specified credentials for the SQL Server service are not valid." error during cluster node installation

While trying to add a node to a SQL Server 2008 R2 cluster, I encountered a problem where the service account credentials were not being accepted during installation. These AD credentials were already in use on the active node.

These are the errors:

[Error Message]
The specified credentials for the SQL Server service are not valid. To continue, provide a valid account and password for the SQL Server service.

[Details]
Microsoft.SqlServer.Configuration.SqlEngine.ValidationException: The specified credentials for the SQL Server service are not valid. To continue, provide a valid account and password for the SQL Server service.
================================================================================

[Error Message]
The credentials you provided for the SQL Server Agent service are invalid. To continue, provide a valid account and password for the SQL Server Agent service.

[Details]
Microsoft.SqlServer.Configuration.Agent.InputValidationException: The credentials you provided for the SQL Server Agent service are invalid. To continue, provide a valid account and password for the SQL Server Agent service.
================================================================================

I knew the AD account credentials I was trying to use were correct. After searching and seeing numerous suggestions such as making sure you have entered the correct password, I decided to follow one of the suggestions: Reboot and try again!

Sure enough, after reboot, the install progressed past the credentials screen without difficulty and the node was added to the cluster.

Since I didn't see the error published along with a concise "do this to fix" response out there, and since several reports of this error were on versions older than SQL 2008 R2, I decided to publish this to hopefully save you some time. One note, make sure that your service account has local admin to the node, at minimum for the duration of the install.

Wednesday, December 11, 2013

Fix: Reorganize Index Task fails in Maintenance Plan

If you are using the Maintenance Plan creator in SSMS, you may have observed that your Reorganize Index tasks are failing. The error is difficult to read unless you log the result to a file.

This is due to one or more indexes not allowing PAGE LOCKs which are required for executing ALTER INDEX REORGANIZE. ALLOW_PAGE_LOCKS = ON has to be set for each index you wish to reorganize.

I found this code on SQL Server Central which will ALTER all indexes to allow PAGE LOCKs. Thank you Ranjith:

Use this query to determine if you have any indexes that disallow page locks:

SELECT A.Name AS InName,ob.Name AS DBName
FROM sys.indexes A
LEFT OUTER JOIN sys.objects ob ON ob.object_id=A.Object_id
WHERE ALLOW_PAGE_LOCKS=0 and ob.type='U'


And here is the full code block to allow page locks:

SET NOCOUNT ON
DECLARE @DBName nvarchar(50), @INName nvarchar(50)
DECLARE @ODBName nvarchar(50), @OINName nvarchar(50)
Declare @execstr nvarchar(200)
DECLARE Index_cursor CURSOR FOR
SELECT A.Name AS InName,ob.Name AS DBName
FROM sys.indexes A
LEFT OUTER JOIN sys.objects ob ON ob.object_id=A.Object_id
WHERE ALLOW_PAGE_LOCKS=0 and ob.type='U'
-- Select only allow_page_locks 0 and User Tables
OPEN Index_cursor
FETCH NEXT FROM Index_cursor
INTO @INName, @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @DBName +' ' + @INName
SET @ODBName = ltrim(rtrim(@DBName))
SET @OINName = ltrim(rtrim(@INName))
SELECT @execstr = 'ALTER INDEX '+@OINName+ ' ON '+
@ODBName+' SET (ALLOW_PAGE_LOCKS = ON)';
EXEC (@execstr);
FETCH NEXT FROM Index_cursor
INTO @INName, @DBName
END
CLOSE Index_cursor
DEALLOCATE Index_cursor


Thursday, September 12, 2013

Fix: “To run a ssis package outside of sql server data tools you must install send successful email of Integration Services or higher.”

This error was seen with SQL Server 2012 version component (SSIS, DB Engine, Agent):

Error: “To run a ssis package outside of sql server data tools you must install send successful email of Integration Services or higher.”

Scenario:

Host 1 - SQL Server 2012 DB Engine and Agent.
Host 2 - SSIS 2012

Package would execute successfully if executed from Host 2. If executed from Host 1 (where the Agent resides, with the ultimate intention of running as a job), it produces the error. Send Mail Task was the focus of the error.

To fix, SSIS 2012 components had to be installed on Host 1.

*This is not a desired solution, as the intention was to keep SSIS components on Host 2 only. If anyone has a solution that does not require SSIS to be installed on Host 1, please post and I'll update the info.

Please see this post for additional info.

Fix: SSIS Warning of Truncation From Database to Data Flow Column

The error appears as follows, even after verifying matching source --> destination length.

Validation warning. [PackageName] {GUID}: Truncation may occur due to retrieving data from database column "[SourceColumnName]" with a length of (x) to data flow column "DataFlowColumnName]" with a length of (y).

To fix, open the data flow task. Select columns, then deselect the columns, and re-select all needed columns.

This was seen with SSIS 2012.


Monday, March 25, 2013

Fix: Unable to shrink file

You've cleared DB connections, CHECKPOINTed, took a backup, but still can't shrink a file.

Grow the file slightly, even just 1 MB.

Then try the shrink again.

Here's a script you can use: http://www.sqlservercentral.com/Forums/Topic647662-5-1.aspx#bm648809

Monday, June 18, 2012

Fix: SQL Server setup fails with error: "MsiGetProductInfo failed to retrieve ProductVersion for package with Product Code" (GUID)

When running SQL Server setup, SQL Server setup fails with error: "MsiGetProductInfo failed to retrieve ProductVersion for package with Product Code" which is followed by a GUID.




To fix this issue, registry entries (possibly from a previous aborted install) must be removed.

To fix, first make sure there is no installation running currently. If you have the above error on screen, close it, and exit SQL Server Setup entirely.

To locate the offending registry entries, search the registry for the first block of the GUID and remove any entries found. Then, search the registry again, except reverse the order of the first GUID block. Remove any entries found. I note that XML Parser v6 is cited when you locate the offending registry keys.





After these keys are removed you will be able to complete the install.

Thank you to Gaurang Patel for his blog post that helped me resolve this issue.

This was seen with SQL Server 2008 R2 Enterprise Edition Setup.