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.

 

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.

could not register the SPN Kerberos

The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

There was another problem that I faced today for a different client. The solution to this one was similar to the SSPI context error.

We installed a SQL Server instance and when I tried to start it, it wouldn't start. Saw the above mentioned error in the event viewer and SQL Server error logs.

The solution was

1) Change the SQL Server Service to run under 'LOCALSYSTEM' account and restart SQL Server.
2) Once this was complete I was able to connect to the instance.
3) Next change the service to run under whatever Active directory account you want to run it under.
4) Restart SQL Server

can not generate sspi context

Cannot generate SSPI context.

Today I was getting the above error while trying to connect to SQL Server instance.





 After some research found that this is related to the registration of the Service Principal Name (SPN) for SQLServer.

The solution to this problem was

1) Change the SQL Server Service to run under 'LOCALSYSTEM' account and restart SQL Server.
2) Once this was complete I was able to connect to the instance.
3) Next change the service to run under whatever Active directory account you want to run it under.
4) Restart SQL Server

This seems to have fixed the problem.

The file 'microsoft.reportviewer.winforms version 9.0.0.0 culture neutral publickeytoken..cannot be opened.

The file 'microsoft.reportviewer.winforms version 9.0.0.0 culture neutral publickeytoken..cannot be opened. Do you want to remove the reference to it from the Recent list?

Recently I did a SQL Server install and everything went fine. But then I tried to run a disk usage report on one the databases and got the above mentioned error

According to a ticket with Microsoft and this is a known issue.
http://connect.microsoft.com/SQLServer/feedback/details/523972/sql-2008-r2-nov-ctp-management-studio-reports-fail-on-opening

You can verify this by checking if you have the following folder.
"C:\Program Files (x86)\Microsoft Visual Studio 9.0\ReportViewer"

There are two workarounds for this issue.
1) Download and install the Reporting Services Report Viewer 9.0. You can download this package from the Microsoft Download Center or by using the following link:
 http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=6ae0aa19-3e6c-474c-9d57-05b2347456b1

2) Additionally you can install "Business Intelligence Development Studio" using the same installation media that you used to install SQL Server. You can also download this from Microsoft's website. I guess you didn't install this while installing SQL Server.