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.

 

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.
 

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