If you’re running Windows Server Update Services (WSUS) 3.0 Service Pack 2, have enabled SSL (the default I think) and now have Windows 8.1 Update clients, you’ll find that those clients are unable to connect to the WSUS server to check for updates.
I am writing this blog post to collect all symptoms and all workarounds and solutions in one place.
The symptoms are as follows:
- Windows Update fails with Code 80072F8F
Lots of material has been written about this Windows Update error. While it all relates to SSL/TLS failures, there is likely nothing wrong with your computer’s time or root certificates if it’s only happening to Windows 8.1 Update (KB 2919355) machines.
- System Event Log Event ID 36888 from Schannel source
Schannel is the Windows component responsible for establishing “secure channels,” like SSL and TLS.
If you arrived at this page looking for info about this error but not related to WSUS, try eventid.net.
If you try to get updates online from Microsoft Update, you will find that it works because Microsoft’s servers are configured to accept TLS 1.2 connections.
The cause of the problem is that Windows 8.1 Update will check for updates using the TLS 1.2 protocol, which is not enabled by default on Server 2008 R2 and not at all available on earlier versions.
Solution and Workarounds
For WSUS running on Windows Server 2008 R2, my recommended action is to enable TLS 1.2. It requires a registry edit for which I’ve provided the contents of a .reg file below. You can also use the workarounds for older versions below, but those are less attractive options, IMHO.
Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2] [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server] "Enabled"=dword:00000001 "DisabledByDefault"=dword:00000000
If you would rather not edit the registry yourself or want to enable numerous protocols and ciphers all at the same time, you may be interested in a tool by Nartac Software that can do it for you: https://www.nartac.com/Products/IISCrypto/Default.aspx
[Please note: I have not tested this tool and can't vouch for its effectiveness or reliability.]
You will need to reboot the server after making the change.
For WSUS running on Windows Server 2008 and earlier, you will need to either
- Disable SSL until the fix is available
- Manually have those clients check Microsoft Update
Enabling TLS 1.2: KB 245030
WSUS Product Team Blog: Windows 8.1 Update (KB 2919355) prevents interaction with WSUS 3.2 over SSL
No one else I know, until Facebook announced they would buy them.
I don’t have the WhatsApp app or an account either, so I immediately classified the phishing message below as spam. However, it is a great example of how criminals will use current events in attempts to get their phishing messages looking legitimate.
The “Autoplay” link goes to a PHP file on an Argentinian domain (acquavendingarg dot com.ar), a possibly legitimate web site although it currently has an “under construction” message on it. More than likely, the web site was compromised with malware that does nothing good.
The e-mail sender was sinatsik1967 at fredericks dot com, which was spoofed because SPF lookup failed. The sending client’s IP originated in Thailand. Most likely, the computer that sent the message is part of a botnet and not the actual attacker’s computer. The originating SMTP server was ns11.hostinglotus.net.
With all these red flags (failed SPF lookup and originating in Thailand), I wonder why the Office 365 spam filters didn’t catch it?
P.S.: This message was sent to an e-mail alias I only use to do business with Ticketmaster… You can draw your own conclusions about how spammers got their hands on that alias.
UPDATE: According to an article on techhelplist.com, this is a pharma scam. So no malware apparently. Still, I wouldn’t buy anything off those sites.
by Sven Aelterman.
While working with an SSIS package that uses a custom component (in this case the RegexClean transformation from Konesans) that was upgraded from SSIS 2008 to SSIS 2012, I ran into this error trying to delete it from the designer when I realized I couldn’t work with it:
“SSIS Designer does not allow this component to be deleted.
The task editor did not clean up properly after the task was removed:”
Even though I had the 2012 version of the component installed, I also noticed that the component had the generic icon instead of the custom icon. It seems that this component did not upgrade successfully when the package was upgraded from 2008 to 2012.
Here are a few things you can try if you run into this issue.
If you find out early enough (i.e. right after upgrading): undo the upgrade (you are using source control, right?), make sure both the old and new component versions are installed and retry the upgrade. This is not guaranteed to work, but it’s worth a try.
If you find out late: edit the package XML and update the component’s Class ID (ideally, that wouldn’t have changed if the component developer did their job right) and version number to match the new version number. If you’re unsure about the new version number, simply create a new package, drop the component in place and examine the new package’s XML. This technique requires that the property names and possible values etc. are the same between the old and the new version.
This is the method I was able to use. I had to replace numerous version numbers, including those for references to Type Converters and UI Type Editors. When replacing the version numbers, be sure to also check the PublicKeyToken attribute’s value to make sure it still matches. Again, if the component developer did their job, it shouldn’t change between versions.
If you replaced everything correctly, the next time you open the package in the designer, you should see the component’s actual icon and you should be able to use any custom editors.
If the properties don’t match, then you should manually remove all traces of that component from the package XML. This unfortunately can be tedious if you have new columns that are introduced by a data flow task. If you remove the component’s XML, then any new columns that component added are gone. The designer won’t load until all references to those columns are removed.
A colleague experienced a rather unsettling event today. Their e-mail account was compromised and used to send out scam messages asking for funds to be transferred abroad to most of the e-mail addresses they had ever used to send and receive e-mail.
When your e-mail account has been taken over, you’re in for a world of trouble. A quick reaction is most important, because your e-mail account is most likely used to request password resets for other accounts.
- If you still have access to your account, immediately change the password.
If you no longer have access to your account (i.e. the hacker changed your password), immediately contact the provider. Providers will have special help lines or e-mail addresses you can use to report such incidents.
- When you get access to your e-mail account again, immediately check the following:
- Has any forwarding e-mail address been set?
Once in your account, hackers can forward your e-mail to another account. You might never know that they are still inside your e-mail account if you don’t explicitly check.
- Change your security question and answer.
Security questions/answers are used to reset passwords. Even if the question/answer has not been changed, the perpetrator had access to them and they are compromised.
- Verify any other password reset methods.
These could include mobile phone numbers, alternate e-mail addresses, etc.
- Has any forwarding e-mail address been set?
- Send out notes to your contact list to let them know that you have regained control and to please not take any action (such as send money, etc.)
- Review your account for any account reset e-mails, including in your trash.
Hackers will likely delete those e-mail messages, but they might have missed some. Your e-mail account is the key to many other accounts. Armed with access to your e-mail account, it’s often trivial to reset passwords on other sites.
Of course, if you don’t find any such notification messages, it doesn’t mean that nothing happened to your other accounts. You should ideally have a list of accounts (yes, all 398 of them…). Organize them from high priority to low and verify that you can still log on to those. Most of the time, that should be sufficient: if you can’t log on anymore, it means your password was reset. You’ll have to go through whatever reset procedures exist at that site.
However, some of the time, there are still sites today that will send your password in plain text to your e-mail account. If that happened, the hacker now has access to that account without you knowing. If the site provides a way to look at the last login time and origin, use that to check if it was abused.
- If you used the same password for any other account, immediately reset those also, to different passwords.
And just one more time: DO NOT EVER USE THE SAME PASSWORD FOR MULTIPLE ACCOUNTS – or if you do, approach it from a risk management perspective: your Twitter account is less valuable than your bank account which is less valuable (yes, really!) than your e-mail account. If you have accounts with different “risk profiles,” do not share passwords between them. And if this is over your head, that’s OK: DO NOT EVER USE THE SAME PASSWORD FOR MULTIPLE ACCOUNTS.
Use a password manager, there are lots of them out there. I prefer not to trust cloud providers, such as LastPass, with them. I use an offline password database only: KeePass. I can still transfer my password database (just a file) between devices using a variety of methods.
It’s also best not to turn on auto-submitting password helpers. They’re very convenient, but there are ways in which web pages can be compromised to load (invisibly) login pages from other sites. Your password manager will dutifully fill out the username and password and let it be intercepted by scripts running on the compromised page you are visiting.
I tell my students that “the Internet is evil, or evil is on the Internet.” For all the benefits a large network such as the Internet offers, exercise caution to protect your accounts. Real damage can be done from a continent away!
by Sven Aelterman.
Another good source is here.
I really meant to write a quick post about attending SQL Saturday 234 in Baton Rouge much earlier, but it’s only now that I’ve arrived in Baton Rouge (first time visitor) that I’ve finally been able to sit down and do it.
I am very much looking forward to the event. I haven’t been to a SQL Saturday with such a diverse lineup of topics, but it should be interesting. I consider myself an IT generalist and I will certainly enjoy meeting people with a lot of different backgrounds.
I will be presenting two sessions, one on FILESTREAM (of course…) and one on SSISDB, the SSIS Catalog in SQL Server 2012. The sample scripts are already uploaded to the SQL Saturday site. If you want to dig a little deeper in either one, please come see me.
As part of my research for my Managing SSISDB talk, I came across an interesting script by Mike Davis. The script intends to copy an environment in the SSISDB. Mike does a good job explaining why this is useful. The SSIS Catalog does not provide a UI for that.
Mike’s script seems to work, but it has some shortcomings and a potentially problematic flaw. First, it does not actually create the environment, it leaves that up to the user first. Second, it does not copy the references (which projects the environment is linked to) or the permissions. The third shortcoming, and the potential for problems, is that it just copies sensitive variable values from the source environment to the destination environment. This is risky, because each environment gets its own encryption key and certificate.
I have come up with an improved script that addresses the shortcomings (pending some code to copy the permissions, not done yet). It is a much more complicated script because it involves the use of loops (but I avoided using CURSOR). However, instead of directly inserting rows in the database tables (not officially supported), I am using the published stored procedures in the catalog namespace.
The entire script, with inline comments, is available here. However, the different steps in the script are outlined below.
- Obtain some info about the source environment (name and folder name). To use the script, you only provide it with the source environment’s ID (you can obtain that from the Catalog UI).
- Create a name and description for the new environment.
This is easily customizable for your needs. The new name is the source name + the current date in ISO format. The new description is the source description + newline + “Copied by <username> on <date>.”
- Create the environment, using catalog.create_environment.
- Copy the variables, including decrypting sensitive values, using catalog.create_environment_variable.
- Copy the permissions (not currently included).
- Create references to the new environment in all projects that reference the source environment, using catalog.create_environment_reference.
The code for #6 is more complicated than I’d like it to be, due to Microsoft’s decision to provide two different ways of referencing variables which is based on folder names and environment names instead of IDs. I find it hard to explain, but here’s a go at it.
Understanding the internal.environment_references table
This table contains the information about which projects reference which environments. It uses the ID of the project, but the name of the environment and sometimes the name of the folder of the environment, no IDs there.
Normally, you’d reference an environment to a project using the Catalog UI. You can reference an environment from the current folder or from another folder. If you reference an environment from the current folder, this becomes a relative (type = R) reference. If you reference an environment in a project deployed to a different folder, this becomes an absolute (type = A) reference. R references do not store the environment’s folder name in the internal.environment_references table. The value of the environment_folder_name is NULL for R type references. For A type references, the environment_folder_name is set to the name of the folder. (That’s part of the flaw in Microsoft’s decision… why use names when each folder has an ID available?)
Because there is no restriction to create two environments with the same name in different folders, this poses a problem when you’re reading the contents of the table. It requires that you know which folder the environment you’re copying is stored in. Then, you need to find the environment references that have an environment with the name of your source environment, but you need to have info about the project (you need to join with the internal.projects table for that, using the project_id column). It’s only by knowing the folder in which the referenced project is deployed that you can properly determine if the reference you’re looking at is the reference to your environment or to an environment with the same name in a different folder. But remember the different types of references… what if the environment_folder_name is NULL? See above: it means that the environment is created in the same folder as the project.
That is why there is a fairly complex compound condition in the WHERE clause of the SELECT statement that picks up the next reference:
((R.environment_folder_name = @environment_folder_name AND R.reference_type = 'A' AND F.name <> R.environment_folder_name) OR (R.reference_type = 'R' AND r.environment_folder_name IS NULL AND F.name = @environment_folder_name))
- Part I: If the reference type is absolute, then the environment_folder_name in the table needs to be different from the name of the folder of the project, but the environment_folder_name needs match that of the source environment’s folder name (remember the source and new environment are both in the same folder).
- Part II: If the reference type is relative, then the environment_folder_name in the table needs to be NULL, but the project’s folder name needs to match the folder name of the source environment.
A few more notes
- I am working on providing additional code to copy the permissions.
- You might think it would be nice to be able to create the copy of the environment in a different folder. However, the Catalog UI does provide Move command. So, after you execute this script (and COMMIT the transaction), you can then use the GUI to move the new environment to any folder you like.
by Sven Aelterman.