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.


Tuesday, June 28, 2016

SQL Server 2012: The server could not load the certificate it needs to initiate an SSL connection

      Error: 25641, Severity: 16, State: 0.
      For target, "5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file", the parameter "filename" passed is invalid. Target parameter at index 0 is invalid
      Error: 25710, Severity: 16, State: 1.
      Event session "system_health" failed to start. Refer to previous errors in the current session to identify the cause, and correct any associated problems.
      Error: 25709, Severity: 16, State: 1.

      Failed to verify Authenticode signature on DLL 'd:\MSSQL11.testinstance\MSSQL\Binn\ftimport.dll'.

     The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid.
     The resource database build version is 11.00.6020. This is an informational message only. No user action is required.
     Error: 26014, Severity: 16, State: 1.
     Unable to load user-specified certificate [Cert Hash(sha1) "A607AA6FB12C3DC3BFFCF46EDC3CB2B3C0EC7FA2"]. The server will not accept a connection. You should verify that the certificate      is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.
     Error: 17182, Severity: 16, State: 1.
     TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property. 
     Error: 17182, Severity: 16, State: 1.
     TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or      property. 
     Error: 17826, Severity: 18, State: 3.
     Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
     Error: 17120, Severity: 16, State: 1.
     SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

I have this server where SSL encryption is enabled. I had a certificate installed on this server and FORCED ENCRYPTION enabled.

But today I received the following error while restarting the SQL Server services.
Although the error points to some issue with the certificates, what it doesn't mention is the the account running the services is not part of the ADMIN group. 

I had just changed the SQL Server services to run under a different account and all I had to do was add that to the ADMINISTRATORS group and I was able to restart the SQL Server.

Thursday, April 21, 2016

This may be due to a connection failure, timeout or low disk condition within the database. For more information about this error navigate to the report server on the local server machine, or enable remote errors.

SQL Server 2008 R2/2012/2014: ReportServer error report Subscription error

This may be due to a connection failure, timeout or low disk condition within the database.
(rsReportServerDatabaseError)
For more information about this error navigate to the report server on the local server machine, or enable remote errors.

The EXECUTE permission was denied on the object 'sp_help_category', database 'msdb', schema 'dbo'.


As part of our new security policies, I had to revoke permissions from the PUBLIC group. Also, I had to revoke SYSADM authority from certain users. This is typical when the application group requests highest authority since they don't want to spend the time to find what permissions are actually required by the application.

After the permissions were revoked, the users reported that above errors when they were trying to add subscriptions to the reports in SQL Server Reporting services.

But the message didn't mention that the error was or what permissions were missing.. To identify the error you have to complete the following two steps to identify the error.

1) Set the 'EnableRemoteErrors' configuration in the ReportServer to TRUE. This will show the exact error in the error log file for ReportServer.
To do this connect to ReportServer instance from SQL Server Management studio and right click on the server and go to Properties.
Then go to the ADVANCED and set the parameter EnableRemoteErrors to True.
You can find the current value by running the following command against the database server.

Use ReportServer
select * from dbo.ConfigurationInfo
where Name ='EnableRemoteErrors'

Once you make this change, you might have to restart the Report server instance to complete the change. Now redo the task that you were trying to complete in the Reports page.

Now check the error file in the ReportServer error log directory.

This will display the exact message in the file. In our case, I saw the following two messages.

This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: The SELECT permission was denied on the object 'syslogins', database 'mssqlsystemresource', schema 'sys'

This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'sp_help_category', database 'msdb', schema 'dbo'.

This clearly shows what permissions are missing. However, we still don't know which user requires these permissions. So get that information run the SQL Server profile which is the second step.

2) Run the SQL Server Profiler to capture the exact message. But as always remember that SQL Server profiler is a very powerful tool and if you capture all the events then you can potentially bring down the server and also collecting too many events will make the analysis very difficult.

So open SQL Server Profiler and capture the event called 'User Error Message' in 'Errors and warnings'






















Once you run the profiler, you can try to execute the same task in Reporting Services and try to recreate the error. In my case, I captured the event 'Audit Schema Object Access Event' but that didn't help much so you can avoid that event.













You can see the same error being reported here as well. If it was a permissions issue then for that given record you will see the value 229 in the column for 'Error' in profiler. Now look int he column for 'LoginName' and you should be able to find the name of the login that is missing that permission.

Lastly, grant that permission and see if that fixes the error. However, I always recommend to grant only the permission required and not dbowner or SYSADM.






Friday, June 26, 2015

Sybase 15.7: Add new device and extend the database

As mentioned in my previous post I have been working on Sybase databases too. Firstly, let me tell you how similar they are and it's relatively easy transition for someone who knows SQL Server and can work around in a UNIX environment.

One of my first problems that I had to resolve was to increase the size of a database that was getting full.

Looks like you have to first create a physical device/file before assigning it to the database. So I following the commands.


1) First add a new device
    USE master
    go
    DISK INIT
    NAME='GLUT_UDL04',
    PHYSNAME='/home/dump/sybase_dump/sybdata/GLUT_UDL04.dat',
    VDEVNO=18,
    SIZE='30G',
    VSTART=0,
    CNTRLTYPE=0,
    DSYNC=FALSE
    go

2) Then Alter the database and add this device.

    ALTER DATABASE SYBPR
    ON GLUT_UDL04='9215M'
   WITH OVERRIDE
    go