Tag Archives: Installation

Articles on Microsoft SQL Server Installation

#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

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

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

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.).

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.

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).
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.

Identifying the user running the SQL Server Database Engine service

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

Choosing the appropriate account running the SQL Server service

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.

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

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’

1 Reply

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

2 Replies

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;

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”

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

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.

SQLTwins by Nakul Vachhrajani

SQL Server tips and experiences dedicated to my twin daughters.

Tag Archives: Installation

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

Solution

References

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

The Solution

Further Reading

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

The Solutions

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

The problem

The Solution

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

Wrapping it up…

Further Reading