Wednesday, April 19, 2017

Error: 18456, Severity: 14, State 38

Login failed for user [User]
Error: 18456, Severity: 14, State 38

I have seen this error multiple times and typically it involves creating a user for that login.
However, today I noticed this on a new server that I configured as a Disaster Recovery.

State 38 means 'Login valid but database unavailable (or login not permissioned)'.
I noticed that on production this account was listed as the DB OWNER since this was used to create the database. Hence, I didn't have to explicitly create the user in the database.

However, on the DR server I had to create this user in the database and grant it dbowner.
When we failed back we had the same error so I was puzzled. Since this account was working fine before we failed over to DR. So there was no way it would not work after we crawl back to PROD.

I noticed that there are multiple ways I can fix this error.

1) Create the user and grant dbowner to the user.
2) Else execute the sp_changedbowner and make this use the owner. This will give implicit permissions on the database.

Error: 17806, Severity: 20, State: 2.

Error: 17806, Severity: 20, State: 2.
SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: xxx.xxx.x.x]

Error: 18452, Severity: 14, State: 1.
Login failed for user ”. The user is not associated with a trusted SQL Server connection. [CLIENT: xxx.xxx.x.x]

SQL Server 2012/2014

I have seen several types of login errors but this one seems to be a confusing one. Today while trying to connect from a third party I noticed that I had three options for Authentication


  • SQL Server Authentication
  • Windows Authentication
  • NTLM2 Windows Authentication

When I tried using just 'Windows Authentication' I got the above error, so I created the login explicitly and it worked. But then I tried to use the 'NTLM2 Windows Authentication' the login was successful. To confirm this I ran the below query to confirm that SQL Server was using NTLM authentication. I was connected from a client when I ran this query.

SELECT DISTINCT auth_scheme FROM sys.dm_exec_connections

So lesson for today is to use NTLM authentication when you get this error. However, I am going to investigate what client I can use which has this authentication option.


Thursday, April 6, 2017

The EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 300)

SQL Server 2008 R2/2012/2014/2016

Today a developer contacted me to get this error fixed. He was trying to debug the query from SSMS. When I tried to do that as a SYSADMIN I got the following error

To continue debugging, the firewall needs to be configured. 
Configuring the firewall requires elevated privileges.

To resolve the second error I had to login to the server and not try this remotely. But the first error still persisted for the a non privileged user.

Even after granting EXECUTE on that SP, the user was still complaining on that error. After some investigation I came to know that this is by design and only SYSADMINs can run the queries in debugging mode. This doesn't make sense to me but I guess I have no option. I am going to try this from Visual Studio and see if I can debug.

Following are some of the references for this info. 
https://connect.microsoft.com/SQLServer/feedback/details/351698/msit-mso-debugging-sql-query-fails-with-an-error-the-execute-permission-was-denied-on-the-object-sp-enable-sql-debug

https://msdn.microsoft.com/en-us/library/w1bhybwz(VS.80).aspx

http://www.sqlserver-dba.com/2012/09/sql-server-user-could-not-execute-stored-procedure-sp_enable_sql_debug.html

Wednesday, October 26, 2016

Restore the missing Windows Installer cache files SQL Server service pack upgrade

The cached MSI file 'C:\Windows\Installer\******.msi' is missing.
Its original file is 'sql_engine_core_inst_loc.msi' and it was installed for product 'SQL Server 2008 R2 Database Engine Services' 
from 'E:\en_sqlserver2008r2ent_x86_x64\1033_ENU_LP\x64\setup\sql_engine_core_inst_loc_msi\'

This error typically occurs when you are trying to install a service pack for a SQL Server instance. The reason for this is simple and I am not sure why Microsoft won't fix it. This is more to do with the Windows installation methods.

Typically the SP file that you download is an executable like SQLServer2008R2SP3-KB2979597-x64-ENU.exe
When you run this it will temporarily extract the files to a directory with weird names like
accb93ea9c80345f38c8b49a3c
efe153398dc16fa539a10f08451096f0

The windows admins might clean up these directories at some later point and this causes the above error. No one should clean up these drives or C:\Windows\Installer. Now that these files are not available you will have to retrieve them from the installation media. Typically you will need the installation files for the SQL Server version and all subsequent SPs and hotfixes that were applied. The steps to retrieve them have been described here
https://support.microsoft.com/en-us/kb/969052

To fix the error do the following

  • Download the file FindSQLInstalls.vbs from the Microsoft support page and then run the following command
  • Cscript FindSQLInstalls.vbs %computername%_sql_install_details.txt
  • Check the text file and following is an example of one file that is missing and how to resolve it
============================================================

PRODUCT NAME   : SQL Server 2008 R2 SP2 BI Development Studio
============================================================
  Product Code: {312E8540-0799-45D5-A02E-DFB8FCA93CCA}
  Version     : 10.53.6000.34
  Most Current Install Date: 20160310
  Target Install Location: 
  Registry Path: 
  HKEY_CLASSES_ROOT\Installer\Products\0458E21399705D540AE2FD8BCF9AC3AC\SourceList
     Package    : sql_bids.msi
  Install Source: \x64\setup\
  LastUsedSource: n;1;Y:\DBATeam\SQLSERVER\SQL Server 2008\SQL_SERVER_2008_R2\x64\setup\
 
 !!!! sql_bids.msi DOES NOT exist on the path in the path Y:\DBATeam\SQLSERVER\SQL Server 2008\SQL_SERVER_2008_R2\x64\setup\ !!!!
  Action needed, re-establish the path to Y:\DBATeam\SQLSERVER\SQL Server 2008\SQL_SERVER_2008_R2\x64\setup\
 Installer Cache File: C:\Windows\Installer\2be74f.msi
  !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
 !!!! C:\Windows\Installer\2be74f.msi DOES NOT exist in the Installer cache. !!!!
 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
 
     Action needed, recreate or re-establish path to the directory:
       Y:\DBATeam\SQLSERVER\SQL Server 2008\SQL_SERVER_2008_R2\x64\setup\then rerun this script to update installer cache and results
     The path on the line above must exist at the root location to resolve
     this problem with your msi/msp file not being found or corrupted,
     In some cases you may need to manually copy the missing file or manually
     replace the problem file overwriting it is exist: 
      Copy "Y:\DBATeam\SQLSERVER\SQL Server 2008\SQL_SERVER_2008_R2\x64\setup\sql_bids.msi" C:\Windows\Installer\2be74f.msi

============================================================

Look for the line where it says LastUsedSource. This is where SQL Server expects the file to be. So in my case I had to copy the installation media for SQL Server 2008 R2 to 

Y:\DBATeam\SQLSERVER\SQL Server 2008\SQL_SERVER_2008_R2

You can create this directory or map the directory where the files exists.
If you rerun the vbscript again you will see messages like this
 
============================================================
PRODUCT NAME   : SQL Server 2008 R2 SP2 BI Development Studio
============================================================
  Product Code: {312E8540-0799-45D5-A02E-DFB8FCA93CCA}
  Version     : 10.53.6000.34
  Most Current Install Date: 20160310
  Target Install Location: 
  Registry Path: 
   HKEY_CLASSES_ROOT\Installer\Products\0458E21399705D540AE2FD8BCF9AC3AC\SourceList
     Package    : sql_bids.msi
  Install Source: \x64\setup\
  LastUsedSource: n;1;Y:\DBATeam\SQLSERVER\SQL Server 2008\SQL_SERVER_2008_R2\x64\setup\
 
    sql_bids.msi exists on the LastUsedSource path, no actions needed.
 
Installer Cache File: C:\Windows\Installer\2be74f.msi
============================================================

Notice it says 'no actions needed'. The Vbscript will copy the corresponding msi files to the C:\Windows\Installer directory

If the msi file belongs to a service pack installation then you will see something like this.
 
SQL Server 2008 R2 SP2 BI Development Studio Patches Installed 
--------------------------------------------------------------------------------
 Display Name:    Hotfix 2806 for SQL Server Business Intelligence Development Studio 2008 (64-bit) (KB2659694)
 KB Article URL:  http://support.microsoft.com/?kbid=2659694
 Install Date:    20120702
   Uninstallable:   1
 Patch Details: 
   HKEY_CLASSES_ROOT\Installer\Patches\1C168ABFC95177B41873A43674A1BAFF
   PackageName:   sql_bids.msp
    Patch LastUsedSource: n;1;r:\8e04aef200a6f6509400f3f8270e52\x64\setup\
   Installer Cache File Path:     C:\Windows\Installer\e21fb.msp
     Per SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Patches\1C168ABFC95177B41873A43674A1BAFF\LocalPackage
 
!!!! C:\Windows\Installer\e21fb.msp package DOES NOT exist in the Installer cache. !!!!
 
     Action needed, recreate or re-establish path to the directory:
       r:\8e04aef200a6f6509400f3f8270e52\x64\setup\ then rerun this script to update installer cache and results
     The path on the line above must exist at the root location to resolve
     this problem with your msi/msp file not being found or corrupted,
     In some cases you may need to manually copy missing files or manually
     replace the problem file, 
 
     Copy "r:\8e04aef200a6f6509400f3f8270e52\x64\setup\sql_bids.msp" C:\Windows\Installer\e21fb.msp

Note the KB2659694 and if needed download it which in my case was SQLServer2008R2-KB2659694-x64.exe

Now on command line extract/uncompress the files using this exe to a temporary directory. The option /x is to uncompress the file to a directory.

SQLServer2008R2-KB2659694-x64 /x

Once you have uncompressed the files copy the directory to the location where the files are expected and rename it to whatever directory it expects. In the above example it expects the following directory
r:\8e04aef200a6f6509400f3f8270e52

So copy the uncompressed directory for this SP to R: drive and rename the directory. You can confirm that the files is there by going to 
"r:\8e04aef200a6f6509400f3f8270e52\x64\setup" 

Rerun the vbscript and confirm that for the files it says that msi file exists and no actions are needed.
Copy the entire installation directory and not individual files like they mention in the support page.

You might have to do this for the following
  • SQL Server installation media
  • Service pack files that were applied
  • Hotfixes that were applied.





 




Monday, October 17, 2016

SQL Server 2008 R2/2012/2014 Security Policies

After installing SQL Server and creating the instance you need to focus on the security features and options that are available to you. I have created a set of standards that all DBAs have to follow in my team after creating the instance. Following are the details.

Service Account

  • Change the SQL Server Service account to a domain account. 
  • This should not have admin rights on the server.
  • Use one AD account for the database engine service and another one for the agent service.
  • Use unique AD Account for each database engine but use the same account for all agent services in your environment. If you have jobs that access databases across different hosts then this is required. 

Port Number

  • Change the port number on which you runt he SQL Server service. This prevents from attackers in attacking on the default port.

Authentication

  • If possible do not enable SQL authentication. I prefer Windows authentication for all logins. 
  • If you have to enable SQL Authentication then make sure that you disable the SA account.

Audit

  • Enable both Successful and failed logins to be audited. You can do this under properties for the SQL Server Instance.

Password Policy

  • If you are creating logins using SQL Authentication then you have to enable the  'Enforce Password Policy'. This will ensure that the passwords that users set will have to comply with the Windows password policies. This will typically require 8 or more characters and a combination of several types of characters.
  • Enforce Password Expiration policy so that users are forced to change passwords every 90 days or so. You can check the policy in Windows to find out the expiration days.

Account types and roles

  • As much as possible refrain from creating logins for individuals. 
  • Create logins for AD groups so that you don't have to keep adding new logins. Once a new AD account is added to a group they will inherit the permissions for that group.
  • Create roles that you would know will be used in the database and added logins to those roles. It is easier to modify roles and modify permissions for everyone in that group, rather than doing this for each login.

Monitoring

  • Set up alerts to monitor failed logins.
  • Set up jobs to check on all the accounts that have expired passwords or if the accounts are locked.
If possible script out all of these and run the script once after you finish the install. This way you will never forget any of the above points.

Wednesday, September 14, 2016

Cannot get the column information from OLE DB provider "OraOLEDB.Oracle" for linked server ""

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 4
Cannot get the column information from OLE DB provider "OraOLEDB.Oracle" for linked server "<Linked_Server>"

I was setting up a linked server to an Oracle database and I was able to connect from that server using the sqlplus client. So the client and all the hostname/port information was correct. Even the login was working just fine so was not sure what the 'Access denied' error is related to. I didn't see any message in the alert log on the Oracle server. So as usual after a couple of quick google searches I found the solution.

To resolve this go under Security in Management studio go to Server Objects > Linked Servers ? Providers

Right click on 'OraOLEDB.Oracle' and go to General. There enable 'Allow inprocess' and this should fix the problem.


Friday, September 9, 2016

Login failed for user xxxxxxxxx. (MsDtsSrvr)

The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer.  For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2012 Books online

The developer today was trying to create a new folder in MSDB under the 'Stored Packages' in Integration services. However, he was getting the following error.


On this server we already had a named instance and Integration Services installed. I had recently created another instance for this developer to use and it looked like when he was trying to create the folder in the MSDB folder he was hitting the wrong database instance.

After some Google searches, I found some support pages that were related to this error.
https://msdn.microsoft.com/en-us/library/ms137789.aspx
https://indepthsql.wordpress.com/2012/03/15/ssis-error-the-sql-server-instance-specified-in-ssis-service-configuration-is-not-present-or-is-not-available-this-might-occur-when-there-is-no-default-instance-of-sql-server-on-the-computer-for-m/

The first step was to find this file called MsDtsSrvr.ini.xml. So I right clicked on Integration Services in SQL Server Configuration Manager and looked at the 'Service' tab. There you will find the 'Binary Path' for the Integration Services. You should be able to find that file under that directory. If not just do a file search.

It is typically located in %ProgramFiles%\Microsoft SQL Server\xxx\DTS\Binn. But depending on where you install the binaries this path will change. If you have a named instance then it will be different.

Edit this file in notepad and look for the following lines.

<Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>.\NamedInstance</ServerName>
    </Folder>

The item highlighted in Red will be the Database Instance under whose MSDB Database the package is being stored. I found that the packages were being stored in the first named instance that was created on this server. Next I checked the SQL Server error logs and I saw login failed messages related to this developer in the older instance. This confirmed the problem.

There are two solutions to this problem.
1) Edit this file and enter the name of the instance to which this user already has access to.     
     This would be the new instance that was created.
2) Grant this user full permissions on the MSDB database in the older named instance. 

There are pros and cons for both. I decided to edit this file and point to the new instance since no one was using the Integration services and the old instance together. I guess the previous DBA installed the Integration Service when it was not going to be used for long.