Tuesday, January 22, 2013

Fix : Error: 18456, Severity: 14, State: 12


Recently i faced the issue while creating a DSN to connect to SQL Server from remote machine. I am getting login failure message and below is the error logged in error log file.

Error: 18456, Severity: 14, State: 12

Login failed for user 'admin'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors

1. No issue with login name and password as i am able to connect to SQL Server using SSMS.
2. If we specify sysadmin to the failed login then DSN is creating without any issues.

I have executed the below commands to fix the issue in my environment, 

GRANT CONNECT SQL TO "admin"

GRANT CONNECT ON ENDPOINT::"TSQL Default TCP" TO "admin"

As a quick resolution you can give sysadmin role to user and create dsn and remove the sysadmin role.

 

Saturday, January 19, 2013

MSIInstaller Application Log Entries Fix - SQL Server 2012 SP1



If you are SQL Server 2012 is running with SP1 (11.0.3000) you may encounter the below error messages in the windows application event log and it may cause high cpu pressure on the machine

In Windows Event Viewer

SQL_Tools_ANS component failed, The resource doesn't exist
SQL_Tools_ANS failed during request for component
Event ID 1004 MSIInstaller / Detection of Product {xxxxxx} feature
Event ID 1040 MSIInstaller / Beginning a windows installer transaction {xxxxxxxxx}
Event ID 1042 MSIInstaller / Ending a windows installer transaction {xxxxxxxxx}

Resolution

Please install CU1 on top of SQL Server 2012 SP1, Build No - 11.0.3128 will fix the MSIInstaller issue and high cpu issue.
 

Friday, January 18, 2013

How to uninstall service pack of SQL Server

Sometimes we may need to uninstall service pack or cumulative updates

To uninstall
1. Open Control Panel
2. Programs and Features - Uninstall a Program
3. Click on Installed updates
4. Choose what is the build number you want to remove
5. Click Remove / Uninstall

 

Tuesday, January 15, 2013

List Mirroring Enabled Databases

--- Run from the Principal Server


SELECT @@SERVERNAME as Current_Instance,
substring(replace(sm.mirroring_partner_name,'TCP://',''),1,charindex('.',(replace(sm.mirroring_partner_name,'TCP://','')))-1) as Mirror_Server,
sm.mirroring_partner_instance as Mirror_Instance,
sd.name as Database_Name,
sm.mirroring_role_desc,
CASE
WHEN sm.mirroring_state is NULL THEN 'Mirroring not configured'
ELSE 'Mirroring configured'
END as Mirroring_Configuration,
sm.mirroring_state_desc,
sm.mirroring_safety_level_desc,
sm.mirroring_witness_name,
sm.mirroring_witness_state_desc
FROM
sys.databases sd
INNER JOIN sys.database_mirroring sm
ON sd.database_id = sm.database_id WHERE sd.database_id > 4
AND sm.mirroring_state is not null


--- Run from the Mirror Server

SELECT @@SERVERNAME as Current_Instance,
substring(replace(sm.mirroring_partner_name,'TCP://',''),1,charindex('.',(replace(sm.mirroring_partner_name,'TCP://','')))-1) as Mirror_Server,
sm.mirroring_partner_instance as Mirror_Instance,
sd.name as Database_Name,
sm.mirroring_role_desc,
CASE
WHEN sm.mirroring_state is NULL THEN 'Mirroring not configured'
ELSE 'Mirroring configured'
END as Mirroring_Configuration,
sm.mirroring_state_desc,
sm.mirroring_safety_level_desc,
sm.mirroring_witness_name,
sm.mirroring_witness_state_desc
FROM
sys.databases sd
INNER JOIN sys.database_mirroring sm
ON sd.database_id = sm.database_id WHERE sd.database_id > 4
AND sm.mirroring_state is not null

Tuesday, January 8, 2013

SQL Agent Jobs are not running even they are enabled


SQL Server Agent Jobs are not invoked automatically even though they are scheduled and enabled.
Below steps resolved my issue, i am not recommend to execute the below commands but it works for me.

use msdb
select * from syssubsystems
go
delete from syssubsystems
go
select * from syssubsystems  -- Make sure everything is deleted

exec msdb.dbo.sp_verify_subsystems 1  -- Everything will be re-created into syssubsystems table.

Restart the SQL Server Agent



 

Thursday, January 3, 2013

Error 18456 Login Failure SQL Server

SQL Server will capture all the failed login attempts into error log by default and also we can view the login failures from the event viewer.

Login failed for "sa" Reason: Password did not match that for the login provided [Client : xx.xx.xx.xx]

18546 States
State 8 - Receives when password is wrong for the specified user.

State 5 - Receives when the specified login doesn't exists on the server.

State 1 - Received when the specified login is disabled at server.


Below query displays error codes of the login failures in SQL Server 2008 where we can get the information about those error codes using net helpmsg

SELECT CONVERT (varchar(30), GETDATE(), 121) as Run_Time,
dateadd (ms, (ST.[RecordTime] - sys.ms_ticks), GETDATE()) as [Notification_Time],
ST.* , sys.ms_ticks AS [Current Time]
FROM
(SELECT
RBXML.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [ErrorCode],
RBXML.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [CallingAPIName],
RBXML.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [APIName],
RBXML.value('(//Record/Error/SPID)[1]', 'int') AS [SPID],
RBXML.value('(//Record/@id)[1]', 'bigint') AS [Record Id],
RBXML.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
RBXML.value('(//Record/@time)[1]', 'bigint') AS [RecordTime]
FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR') AS RB(RBXML)) ST
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY ST.[RecordTime] ASC
From the output we can see hexadecimal error codes like 0x534 = 1332 (decimal), 0x139F = 5023.
We can get the error message by running the below command from the command prompt

net helpmsg 1332
No mapping between account names and security IDs was done.

net helpmsg 5023
The group or resource is not in the correct state to perform the requested operation.

 

NET Framework execution was aborted by escalation policy because of out of memory

.NET Framework execution was aborted by escalation policy because of out of memory

The issue was resolved by doing the below changes on our environment

1. Adding service account to lock pages in memory
2. Adjusting Min and Max Memory in optimized way.

CLR memory consumtion comes outside of buffer pool, by configuring max memory in optimized way to give enough memory for CLR.