Tag Archives: Installation

Articles on Microsoft SQL Server Installation

SSDT 15.5.2 for Visual Studio 2017 Installation Error: 0x80072f76

#0411 – SQL Server – SSDT 15.5.2 for Visual Studio 2017 – Installation failed with error 0x80072f76: Failed to acquire payload


I was recently building up an all-in-one development environment for a project and ran into an unexpected error. I had already installed Microsoft Visual Studio 2017 and attempted to install SQL Server Data Tools (SSDT).

The SSDT 15.5.2 for Visual Studio 2017 failed to install with the following error.

SSDT 15.5.2 for Visual Studio 2017 Installation Error: 0x80072f76

SSDT 15.5.2 for Visual Studio 2017 Installation Error: 0x80072f76

Upon studying the error log file, I found the following sequence of unexpected entries:

Acquiring package: Microsoft.DataTools.AnalysisServices, payload: pay98911873C1CF2F7FF48824555D2B0337, download from: https://go.microsoft.com/fwlink/?linkid=866936
Error 0x80072f08: Failed to send request to URL: https://go.microsoft.com/fwlink/?linkid=866936, trying to process HTTP status code anyway.
Error 0x80072f76: Failed attempt to download URL: 'https://go.microsoft.com/fwlink/?linkid=866936' to: 'C:\Users\sqltwins\AppData\Local\Temp\2\{5C5CD709-A276-454C-88E3-0E939CB80B0E}\pay98911873C1CF2F7FF48824555D2B0337'
Error 0x80072f76: Failed to acquire payload from: 'https://go.microsoft.com/fwlink/?linkid=866936' to working path: 'C:\Users\sqltwins\AppData\Local\Temp\2\{5C5CD709-A276-454C-88E3-0E939CB80B0E}\pay98911873C1CF2F7FF48824555D2B0337'
Failed to acquire payload: pay98911873C1CF2F7FF48824555D2B0337 to working path: C:\Users\sqltwins\AppData\Local\Temp\2\{5C5CD709-A276-454C-88E3-0E939CB80B0E}\pay98911873C1CF2F7FF48824555D2B0337, error: 0x80072f76.
MainViewModel.OnPackageAction: Install CompletedDownload for package SQL Server Analysis Services (id: Microsoft.DataTools.AnalysisServices)
Error 0x80072f76: Failed while caching, aborting execution.

From the error log entries, it is clear that the installer program was unable to access a particular URL in order to download the respective installer components.

So, I took the URL “https://go.microsoft.com/fwlink/?linkid=866936”, pasted it in the address bar of a browser and immediately ran into a problem:

Your current security settings do not allow this file to be downloaded.
Enhanced Security Configuration (ESC) preventing file downloads

Enhanced Security Configuration (ESC) preventing file downloads

This clearly indicates that the Internet Explorer Enhanced Security Configuration (IE-ESC) was preventing the download and in-turn resulting into the error.

Solution

I immediately added microsoft.com to the “trusted sites” zone and restarted the installer. This time, the installer completed successfully! (One may suggest to disable Enhaned Security Configuration altogether, but that is not recommended due to the obvious security reasons.)

SSDT 15.5.2 for Visual Studio 2017 Installation continues after necessary package URLs are allowed in Enhanced Security Configuration

SSDT 15.5.2 for Visual Studio 2017 Installation

Hope this helps you someday when you are setting up your environments.

References

  • Download SQL Server Data Tools (SSDT): https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt
  • Internet Explorer Enhanced Security Configuration (ESC): https://support.microsoft.com/en-in/help/815141/internet-explorer-enhanced-security-configuration-changes-the-browsing

Until we meet next time,

Be courteous. Drive responsibly.

#0405 – SQL Server – Msg 5133 – Backup/Restore Errors – Directory lookup for file failed – Operating System Error 5(Access is denied.).


We got a new server recently and one of my colleagues ran into an error when restoring a database. The error was a quite generic (reformatted below for readability):

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file 
"C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Data.mdf" 
failed with the operating system error 5(Access is denied.).

Msg 3156, Level 16, State 3, Line 1
File 'AdventureWorks2014_Data' cannot be restored to 
'C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Data.mdf'. 
Use WITH MOVE to identify a valid location for the file.

My immediate reaction was to  review the restore script.

USE [master];
GO
RESTORE DATABASE [AdventureWorks2014]
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak'
WITH
MOVE 'AdventureWorks2014_Data' TO 'C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Data.mdf',
MOVE 'AdventureWorks2014_Log' TO 'C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Log.ldf';
GO

All looked well, I subsequently moved to the environmental aspect of troubleshooting. It was a new server and we had just created the target folders to which the database was to be restored.

We attempted to restore to the default database locations configured during SQL Server installation and the restore worked. So, one thing that became clear: the SQL Server service did not have appropriate security rights on the destination folder.

The Solution

Once we determined that it was the security rights on the destination folder, the only thing remaining was to grant the rights. Here’s how we do it.

  1. Cross-check the user set as the service user for Microsoft SQL Server database engine (use the SQL Server Configuration Manager for interacting with the SQL Server service – here’s why).
  2. Under Folder properties, ensure that this user has full security rights (or at least equivalent to the rights assigned on the default database folders specified at the time of installation)

Here’s are detailed screenshots showing the above process.

01_SQLServerConfigurationManager

Identifying the user running the SQL Server Database Engine service

02_GrantingPermissions_01

Navigating into file system folder security options to grant access to the SQL Server service

02_GrantingPermissions_02

Choosing the appropriate account running the SQL Server service

02_GrantingPermissions_03

Applying appropriate rights to folder

By the way: If you encounter similar issues in accessing your backup files, the root cause and solution are the same. Check the permissions on the folders housing your backups and you should  see that the database engine does not have the necessary security rights.

Further Reading

Maintaining permissions on data folders and appropriate registry entries is something that is handled by the SQL Server Configuration Manager when you change the service account under which  the database engine is running. If you use services.msc (the old way), this is not done and your SQL Server may stop working.

  • Changing SQL Server Service Account or Password – Avoid restarting SQL Server [Blog Link]
  • Blog Post #0344 – SQL Server – Missing Configuration Manager on Windows 8 [Blog Link]

Until we meet next time,

Be courteous. Drive responsibly.

#0402–SQL Server 2016 – KB3207512 fails–Msg 17054 – Unable to shutdown the instance; Operating System Error 21 (The device is not ready)


I recently updated my personal sandbox to use SQL Server 2016. While the installation succeeded, one of the first few problems that I ran into were:

  • The SQL Server 2016 instance failed to shutdown
  • Error 17054 was logged every time a shutdown is attempted with the error: “The current event was not reported to the Windows Events log. Operating system error = (null). You may need to clear the Windows Events log if it is full.
  • Installation of KB3207512 (update for SQL Server 2016 SP1 Reporting Services) and latest CUs kept failing
  • User databases would not be accessible with an error: “The operating system returned error 21(The device is not ready.)

While I was trying to figure out what was wrong, I ran into the same problem with a few other instances.

The Solutions

After a lot of rounds of trial and error, the following changes finally did the trick. The items below collectively make up the solution and all items need to be performed in order to get the SQL server instance up-to speed again.

  • Launch the SQL Server Configuration Manager
  • Under “SQL Server Network Configuration” ensure that the TCP/IP protocol is enabled
  • Under “SQL Server Services”, ensure that the following services are started:
    • SQL Server PolyBase Engine
    • SQL Server PolyBase Data Movement

Once the steps provided above are done, all the problems listed above should cease to exist.

Until we meet next time,

Be courteous. Drive responsibly.

#0372 – SQL Server -SSIS – VSTA, Could not load file or assembly ‘Microsoft.VisualStudio.Tools.Applications.Core’


We use SQL Server Integration Services (SSIS) for all of our data movement and upgrades. Recently, when running a particular SSIS package, one of our new servers threw a strange error:

There was an exception while loading Script Task from XML: System.IO.FileNotFoundException: Could not load file or assembly ‘Microsoft.VisualStudio.Tools.Applications.Core’

I was on call and one of the first things I did was to cover the basics – checking out the environment. The SSIS package file (.dtsx) was located on the file system exactly where I had expected it to be and hence, I was stumped to see the “File Not Found” exception. The strange thing was that the same package worked fine when run on the staging environment and also in my local development environment.

After checking out various things and a break, I refocused my attention to reading the error message again. That’s when the words simply popped out at me – “…while loading Script Task from XML…” – the script task is where the problem was. Script tasks are most commonly used to set connections on the connection managers and that’s exactly what we were doing.

The problem

Script tasks in SSIS leverage the Visual Studio Tools For Automation (VSTA). I therefore headed over to Add/Remove Programs to confirm that VSTA is indeed installed. I only had the 64-bit version installed on the affected server. As with all things SSIS, on a 64-bit system, I had expected to see both the x64 and the x86 version of VSTA installed. Below is a screenshot of the Add/Remove programs from one of my test VMs which is a 32-bit environment (I could not get the actual screenshot because it was a production server).

Screenshot showing the installation of VSTA in a 32-bit environment. In a 64-bit environment, one would expect to see 2 entries - one for the 64-bit version and one for the 32-bit version

Sample VSTA installation on a 32-bit environment (non-production)

The Solution

I immediately contacted the on-call IT team, who kindly shared the SQL Server media from which the installation was done. Just navigating to the following path on the media gave me the 32-bit installable for VSTA. We installed 32-bit version of VSTA and the issue was resolved.

Path on the SQL Server media to get VSTA: ..\redist\VSTA\runtime\x86

Have you ever encountered this or other interesting issues in your deployment experience? How did you troubleshoot them? Do share via the blog comments – I would love to know!

Until we meet next time,
Be courteous. Drive responsibly.

#0368 – SQL Server – Myths – Windows Authentication – Windows login name change does not mandate a change to SQL login


In today’s world, mergers and acquisitions of organizations are a reality. Having been through a couple of M&As in the last couple of years myself, I have had a unique opportunity to experience changes which would not be experienced in the normal course of work.

One such change is the changing of the domain logins. M&As often come with renaming of domain logins to confirm to a common standard. The question that came up in our team was:

What happens to our SQL Servers that use windows authentication if the login name changes?

The answer? Nothing. SQL Server continues to work as usual if the login name is the only thing that changed (and your applications do not use the physical “login name” in any way).

Here’s a demo for clarity.

On my VM, I created a user “OldUser” and logged in to SSMS using Windows Authentication when running as that user.

Screenshot showing the use of an existing windows login for authentication into a SQL Server instance.

Using an Old Login to login to SSMS

For the record, we will also execute the following query:

SELECT SUSER_SNAME() AS LoginName,SUSER_SID() AS LoginSID;
Query showing the Old User's SID

OldUser’s SID

Now, because this is a local login, all I do is rename the Windows login using Computer Management. The same behaviour applies to logins renamed via Active Directory as well.

Renaming the "OldUser" to "NewUser" in Computer Management

Renaming the “OldUser” to “NewUser”

I load the SSMS again – this time as “NewUser”.

Login to SSMS using the new, renamed login

Login to SSMS using the new, renamed login

Notice how the authentication works even though the login name has changed.

Wrapping it up…

Windows authentication in SQL Server is based on the SID. Hence, even though the login name changed, SQL Server allowed me to login because the SID did not change. In fact, this is why when we create or modify a login, the only requirement is to ensure that the SID of the login is same as the one we want to create/modify.

In a practical scenario, the IT teams would not re-generate the SIDs because that would mean too much work – realigning file & folder shares, resetting SSO and what have you. Instead, they simply update the login name (or the human interpretative part of the login).

What this means is that the authentication on the SQL Servers continue to work as usual – even after the login changes. No manual intervention is required!

Note of caution: Because the SQL Login name and the Windows login name are independent, renaming the Windows login will NOT rename the SQL login. The entry under sys.server_principals continues to read the old value. So, if your applications are designed to work based on the login name in sys.server_principals, you need to use ALTER LOGIN statements whenever IT changes the Windows login names.

Further Reading

In case you are really interested in digging deeper into SQL Server,

  • Security considerations for a SQL Server installation [MSDN Link]

Until we meet next time,
Be courteous. Drive responsibly.