SQL Server 2016 and the Internet: Forced Updates, Phoning Home

SQL Server 2016’s End User License Agreement (EULA) contains a couple of surprises for those who let their SQL Servers connect to the internet. No, I don’t mean where the Internet connects to you – I mean where the SQL Server can reach the internet, like open a web page.

Issue #1: You may get updates whether you want them or not.

You probably shouldn’t run 2016 side-by-side with older versions because:

IMPORTANT NOTICE:  AUTOMATIC UPDATES TO PREVIOUS VERSIONS OF SQL SERVER.  If this software is installed on servers or devices running any supported editions of SQL Server prior to SQL Server 2016 RC (or components of any of them) this software will automatically update and replace certain files or features within those editions with files from this software.  This feature cannot be switched off.  Removal of these files may cause errors in the software and the original files may not be recoverable.  By installing this software on a server or device that is running such editions you consent to these updates in all such editions and copies of SQL Server (including components of any of them) running on that server or device.

You consented.

Issue #2: Your SQL Server phones home to Redmond by default.

We collect data about how you interact with this software. This includes data about the performance of the services, any problems you experience with them, and the features you use…. It includes information about the operating systems and other software installed on your device, including product keys. By using this software, you consent to Microsoft’s collection of usage and performance data related to your use of the software.

Before 2016, you had to manually opt-in by checking a checkbox during installation.

With SQL Server 2016, there’s no checkbox – you’re opted in by default.

I’m actually a huge fan of app telemetry – sending crash reports and usage data back to the application developers in order to help make the app better. I want developers to know how I use their apps, because I want them to improve the parts of the app that I use the most. Heck, I’d be fine if SSMS turned on the microphone while I worked, and then did sentiment analysis. (They would see a very high number of four-letter words tied to the term “IntelliSense.”)

Here’s the relevant part of setup:

If you install it, it's phoning home

If you install it, it’s phoning home

The Privacy Statement links to https://www.microsoft.com/EN-US/privacystatement/SQLServer/Default.aspx, which at first glance looks like it has some juicy hyperlinks, but they’re not links. You have to click on the Learn More link at the bottom right:

Would you like to learn more?

Would you like to learn more?

How to Turn Off the Phone-Home Option for Standard and Enterprise Edition

That above link explains:

Enterprise customers may construct Group Policy to opt in or out of telemetry collection by setting a registry-based policy. The relevant registry key and settings are as follows:

Key = HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\130

RegEntry name = CustomerFeedback

Entry type DWORD: 0 is opt out, 1 is opt in

Be aware that editing the registry, much like the Wu-Tang Clan, is nothing to, uh, mess around with.

Issue #3: You can’t Turn Off the Phone-Home Option for Developer, Express, and Evaluation Editions

Jason Ash points out that the KB 3153756 says:

You can disable the sending of information to Microsoft only in paid versions of SQL Server. You cannot disable this functionality in Developer, Enterprise Evaluation, and Express editions of SQL Server 2016.

I’m curious to see how customers react to these new changes. I bet in the days of phone app telemetry, folks are okay with it. I certainly am – as long as we don’t find out that things like memory dumps with end user queries (especially insert statements) are making their way to places unknown.

Update 2016/06/01 – Microsoft’s Jeff Papiez points out KB 3153756: How to configure SQL Server 2016 to send feedback to Microsoft. That KB explains the registry changes required to turn off telemetry, and also lists a couple of sample DMV queries whose results could get sent back to Microsoft.

If you believe you should be able to disable phone-home telemetry feedback for Developer, Express, and Evaluation Editions, vote for this Connect item.

Previous Post
First Day Deal Breakers
Next Post
#DellDBADays 2016: What Would You Do with Unlimited Hardware?

91 Comments. Leave new

  • Forced updates of SQL, Forced phone home, Forced Windows 10, Forced Azure for certain features…see a trend here? Ick.

    Reply
    • Cody Konior
      June 1, 2016 7:46 pm

      I agree, it feels very icky to me.

      Every install, feature additional, and upgrade, you’re going to need to run a PowerShell script to go through and check all of the various registry keys are set to disable this now, because there’s half a dozen or more to keep track of. MinionTelemetryKiller and HallengrenPhoneHomeSolution anyone?

      I have read they’ve allegedly ignored keys like this on the Windows side to force upgrades to 10. What’s the guarantee they won’t do the same here?

      It used to be a check box. That they’ve removed the option and made it all that much harder to turn off is exactly what’s disgusting about it. Despite knowing that people don’t want to give the data they’ve decided to wander in and say, “Well, we want it, so we’re taking it.” And that’s the respect you get for paying 10s to 100s of thousands of dollars for your license and support.

      The KB lists ONE piece of information that they send. They likely send much more that is not documented to this level of detail, and will add it in the future. But this is all irrelevant – it doesn’t matter how much they want or what’s useful to them – NO *should* mean NO.

      Reply
      • Cody Konior
        June 1, 2016 7:50 pm

        Also, turning it off does not function for Developer, Express, or Enterprise Evaluation!

        I didn’t find any documentation on what ports it communicates over or what sites it connects to. That would be nice so we can block it specifically without risking blocking everything.

        Reply
        • Cody – my guess is that the term “Enterprise customers” in the document refers to companies large enough to roll out group policies. Have you seen anything else that indicates the disabling won’t work on other editions?

          Reply
          • Brent,
            It’s actually spelled out in the KB you linked to in the post and below in the comments, in the “What’s new in SQL Server 2016” section:
            “Note You can disable the sending of information to Microsoft only in paid versions of SQL Server. You cannot disable this functionality in Developer, Enterprise Evaluation, and Express editions of SQL Server 2016.”

          • DAAAAAAAAANG, wow, I missed that. Good catch. Updating the post to reflect that.

        • We monitored the network traffic on one server that we upgraded and saw traffic to several different IP ranges at Microsoft and at least 8 different subdomains of microsoft.com. Documenting it would be a long list.

          Reply
          • Conor Cunningham, Architect SQL Team
            January 4, 2017 5:29 pm

            Microsoft maintains a set of addresses to help avoid DoS attacks and load-balancing for the volume of data that is expected over the whole customer base (over multiple products, not just SQL). Please reference the KB article here for how to audit what is sent (which is easier than trying to look only at network traffic: https://msdn.microsoft.com/en-US/library/mt743085(SQL.130).aspx

  • Love the Starship Troopers reference. If I read this correctly, if I install SQL 2016, then I cannot have multiple versions of SQL Server running on it? I have multiple versions because my clients are using them.

    Reply
    • Thanks sir! Yeah, you *can* have multiple versions running – but be aware that the 2016 updates may forcefully break prior versions.

      This is one of the reasons I’m a huge fan of VMs. I never run SQL Server in the base machine, only in VMs.

      Reply
      • What virtual machine software are you using these days? I used to license VMware Workstation, but I believe it’s being discontinued.

        Also, I don’t currently have an MSDN license so I don’t have “extra” Windows licenses.

        Any suggestions for people in a similar situation?

        Reply
  • Jeff Papiez
    June 1, 2016 1:25 pm

    See this KB for more info regarding how to disable telemetry: https://support.microsoft.com/en-us/kb/3153756

    Reply
  • I can see my employer either deploying a GPO to turn off the telemetry or mandating that it be turned off at install by editing the registry.

    I can also see them setting up a firewall policy to block the self-updating (unless that can be overridden by using an internal WSUS server, which we do.)

    Reply
  • Will stopping and disabling the various SQL CEIP services have the same effect as the registry changes?

    Reply
  • If you firewall your servers correctly and cut off their access to the internet, this second “issue” surely doesn’t matter much?

    Reply
    • Thomas – yeah, I just don’t see a lot of folks out there who cut off the SQL Server’s access to the Internet.

      Reply
      • Really?
        I’ve worked with SQL Server for 14 years and I’ve never seen a single SQL Server with an internet access…

        Is that something you see on small and medium company or did you also see it in bigger companies?

        I understand it on developer workstation, but on dedicated SQL Server, why would you do that?

        Reply
      • I believe I’m in that boat. I use developer edition all the time, but I’d prefer not to send info back to Microsoft.

        I bet there’s a workaround involving editing hosts files, but it’s like putting a folded-up napkin under a wobbly table. It might work, but it’s not the classiest thing to have to do.

        Reply
        • Michael – yeah, especially because if I’m MS, I’m not even using DNS names – I’d go with IP addresses for the phone-home. I wouldn’t want to gamble on anybody’s content filtering at the DNS level.

          Reply
    • I’ll be installing ’16 in a VM in the near future: I’ll keep a close eye on my firewall log to see what my computer is talking to, then rabidly blocking ports.

      I will also be advising my current employer to not upgrade any installations to ’16 since we are a school and there’s something called FERPA which is similar to HIPAA except for student records.

      *sigh*

      Back to Stay alert! Trust no one! Keep your laser handy!

      Reply
  • Paul kavanagh
    June 1, 2016 4:51 pm

    Can also be turned off from

    https://www.microsoft.com/EN-US/privacystatement/SQLServer/Default.aspx

    Choice/Control
    You will be given an opportunity to participate in the collection of telemetry during the SQL Server installation. For pre-release versions of SQL Server, telemetry collection is turned on by default. You can later change your installation choice by following the instructions below.
    To change your telemetry collection settings:
    1. Start SQL Server Management Studio, or SQL Server Business Intelligence Development Studio, and open a new or existing Analysis Services, or Data Transformation Services project.
    2. From the Help menu of SQL Server Management Studio, select Microsoft SQL Server Customer Feedback Options, or from the Help menu of SQL Server Business Intelligence Development Studio, select Microsoft SQL Server Customer Feedback Settings.
    3. To turn telemetry collection off, click No, I don’t wish to participate. To turn telemetry collection on, click Yes, I am willing to participate.
    4. Click OK.

    Reply
  • This is why I always check out Brent first. Been following Brent for 9 years now. I get all the little quirks that not many people will publish. Love the reference to Wu-Tang.

    Reply
  • Derek Czarny
    June 2, 2016 11:34 am

    Set up transactional replication with 1tb database, with tables with text columns

    Reply
  • Kenneth AMBROSE
    June 2, 2016 12:17 pm

    I worked for a company that sells retail internet entertainment devices that many millions of people have in their homes. The devices continuously report back home what you are watching, when you are watching, how long you watch, etc. etc. etc., AND RECORDED IT PERMANENTLY IN SQL SERVER DBS. I know, I did the analysis on the captured data.

    Still like the idea of your devices phoning home about whatever they want, whenever they want?

    Reply
  • Well, I can say that I met a person who actually read the terms to which they were agreeing.

    Reply
  • “It is possible that personally identifiable information may be captured”

    Ummm.. out of the gate they are grabbing (this was from a couple RCs ago – I need to re-capture RTM to see if any of its changed):

    “Reserved.SessionId”:”xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx”,
    “Context.Default.SQL.Core.User.Id”:”xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx”,
    “Context.Default.SQL.Core.User.Location.GeoId”:xxx

    That seems pretty identifiable to me.

    “SQL.Core.ConnectionType”:”Standalone”,
    “SQL.Core.ServerVersion”:”xx.x.xxxx”,
    “SQL.Core.ServerOs”:”Microsoft SQL Server 2014 – xx.x.xxx.x (X64) \n\txxx x xxxx xx:xx:xx \n\tCopyright (c) Microsoft Corporation\n\tEnterprise Edition (64-bit) on Windows NT 6.3 (Build xxxxxx: )”,
    “SQL.Core.IsWindowsAuthentication”:”True”,
    “SQL.Core.IsEncryptedConnection”:”False”

    “SQL.Command.Name”:”Edit.Copy”
    “SQL.Command.Name”:”Edit.SelectAll”
    “SQL.Command.Name”:”TableDesigner.SetPrimaryKey”
    “SQL.Command.Name”:”File.SaveSelectedItems”
    “SQL.Command.Name”:”Edit.Delete”
    + tons of others.

    So when I edit->copy – they are somehow going to improve that experience for me?

    To be fair SQL is not half as bad a VS. VS comes complete with a real key logger. My problem is: 1) Connect is full of won’t fix issues – don’t worry about my edit->select all 2) Microsoft is not honest and upfront about what it is collecting 3) There is no off button.

    Reply
  • Allow users to disable auto update feature in SQL Server 2016
    https://connect.microsoft.com/SQLServer/feedback/details/2777795

    Wow,
    As someone who as been in and out of the third party vendor space, some of it heavily regulated, both of these things are just nightmares. The auto update thing is going to be an auto support ticket generator for a ton of companies.

    It feels like they made this decision say in the last month and like a kid with scissors just ran with it.

    Oh well.
    -wes

    Reply
    • Wes, I’ve closed and replied to your connect item, as it is based on incorrect assumptions. This was my reply there:

      “Auto-updates are not a part of SQL Server servicing model.

      We will start publishing SQL Server CUs to the Windows Update Catalog, but they are shown as optional updates that have to be selected by the user for update to happen. We are now recommending proactive, ongoing install of Cumulative Updates. Read more on the ISM model in https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-updates-to-the-sql-server-incremental-servicing-model-ism/

      Then there is SQL Server setup time, which is called the smart setup capability. That has been introduced back in SQL Server 2012, allowing automatic slipstream setup if connected to the internet or an internal update repository. You can read more about that in https://blogs.msdn.microsoft.com/psssql/2012/12/06/sql-server-2012-setup-just-got-smarter/

      And finally, some features that used to ship with SQL Server have moved out of band now, like Management Studio. If connected to the internet, and the option has not been disabled within the tool, a user will get a prompt asking to update Management Studio to the latest release. Again, this is a client tool feature, not an Engine feature.”

      Reply
  • The KB article notes that if you use SSAS, an account with administrative privileges is created. It can be removed, but it really shouldn’t be created in the first place.

    Feedback for Analysis Services

    SQL Server 2016 Analysis Services, during installation, adds a special account to your Analysis Services instance. This account is a member of the Analysis Services Server Admin role and is used to collect information for feedback purposes from the Analysis Services instance.

    You can configure your service to not send usage data, as described in the “Set registry keys on the server machine” section. However, this will not remove the service account. You can manually remove the SSASTELEMETRY account through SQL Server Management Studio, under server properties on the Security tab.

    Reply
  • It should be clear by now that Microsoft wants on-premise SQL Server customers to move to the cloud. To prioritize Azure feature parity with on-premise SQL Server, I assume they have to know, in aggregate, what features their customers are using. As a result, we get forced updates and telemetry.

    Reply
    • I’m definitely not a fan. I will take on-premise and my internal LAN over an internet connection for reliability any day. There may be some other and good reasons to move to Azure, but I have not yet seen a need for it in my work.

      Reply
      • ken ambrose
        June 3, 2016 3:40 pm

        Is there really anyone of any size nowadays who is not hosting their DBs in a remote data center? The security benefits alone would be a huge motivation to do so.

        And if they are using remote data center, internet connection is a given anyway…

        Reply
        • Ken – yeah, medium to large companies often have their own data centers. For example, one of my clients has dozens of SQL Servers, each with 5-50TB of data, all in their own data center.

          Reply
  • Another thought about this… Forcing updates are one thing but forcing an update implies forcing a reboot, at least in some situations. While the Windows 10 forced updates and reboots are annoying but tolerable, forcing a reboot for a production SQL server could have some terrible side effects.

    Reply
  • stephen davies
    June 6, 2016 9:08 am

    The auto update ‘thing’ would also affect SQL2016 only clusters; I might want to apply a service pack to the dev instance in an active/active system to check whether it breaks something, the live one might be affected too and will pick up the changes if there is a fail over. And what about the initial install, could putting another instance onto a cluster break an existing instance? Or during a rolling upgrade? It doesn’t fill me with confidence.

    Reply
  • The comment on the Connect page about how the lawyers are going to switch database vendors is very compelling (i.e. they can’t risk sharing PII with Microsoft accidentally). However, the SQL 2016 Licensing Guide says that actual product/production data is not supposed to be stored in Developer Edition. I am too lazy to see if this is a new verbiage since SQL 2014 – but Microsoft is basically telling you to mask or generate your development data rather than develop against actual data.

    Reply
    • Conor Cunningham, Architect, SQL Team
      June 9, 2016 9:41 am

      Please note that no PII data is collected in the usage data at all.

      If there are specific compliance concerns that any customers have, please contact Microsoft and we can go through any specifics required.

      Reply
  • Conor Cunningham, Architect, SQL Team
    June 7, 2016 12:32 pm

    Brent, with respect I think your post is mixing a few things and could be clearer to avoid confusing SQL users. I will attempt to clarify here, but I hope you will consider separating/editing this post into separate topics and more crisply cover the details.

    First, SQL Server 2016 contains a setup-time feature we call “smart setup”. On servers connected to the Internet, the setup will check to see if there are any critical hotfixes that are needed and download them for you. It so happens we ended up needing to use this capability- we had a problem with the RTM bits that we discovered at the end of the development cycle where a needed patch to the VC runtime was not properly installed. The smart setup feature will download and auto-install this for you if you are connected to the Internet, avoiding crashes in production. You can read about that patch here:
    https://support.microsoft.com/en-us/kb/3164398 . So, you can avoid having to install this patch manually if setup can connect to the Internet and get this patch for you.

    We also have other avenues to install the patch via Windows Update and through a release of a full new installer which can work in environments that don’t connect to the Internet. There is no requirement that you be connected to the Internet to run setup, but in this case it helps you avoid a manual step during the installation process.

    Regarding usage data collection, we did make a change to the product to move from an opt-in to an opt-out model for this collection in SQL Server 2016. Note that SQL Server 2014 and prior also send usage data to Microsoft when sending usage data is enabled. We’ve published a KB article explaining how to disable the feature for each component for customers that have any concerns or regulatory issues. The capability to disable can not be turned off in free editions, and this is a change from prior releases. It is also possible for firewalls to block telemetry transmission if the customer’s network is set up with such a capability (even with free editions, if you are so inclined).

    Please note that this is _not_ a mechanism for delivering features to customers (a la Windows forcing upgrades to Windows 10 or forcing feature packs on customers). It might be clearer if your post separated the smart setup capability from the telemetry capability to make it more obvious that these are not the same features are not identical to what Windows did with Windows 10.

    It is also worth noting that some features in SQL Server, such as Management Studio, have moved to shipping regularly (monthly) outside of a given release of SQL Server. So, please note that there are separate capabilities for how some of those are disabled vs. how SQL Server’s relational engine or Analysis Services components are disabled. We expect to deliver features much more quickly in those components going forward – the reason for splitting them is unrelated to usage data.

    We’re working on improving/clarifying the usage data documentation (which we described to you on the internal MVP forum before you made this post but are not 100% done with yet) so that we can address any concerns that customers may have about this change. Microsoft is not collecting sensitive information about your data in the data sent to Microsoft – most of what is being collected is of the form “are you using feature X” and “is the performance of working as we expected?” or “what errors are happening on this instance of SQL?”. Microsoft is not collecting your passwords or values from inside of user tables in the usage data sent to Microsoft.

    Like in prior versions, sending crash dumps to Microsoft shares the memory state of your server in the crash sent to Microsoft and that _can_ contain user data. Microsoft has policies to restrict the usage of the crash data to improve the product (fix the bug that caused the crash, assuming it is a product bug) and to keep that data within Microsoft. We don’t look through crash dumps except to determine how to fix bugs in our code so that you can get fixes more quickly than you might otherwise get them.

    We are happy to talk with any concerned customers directly if there are specific regulatory concerns about usage data being shared with Microsoft. We are working on a more detailed document to cover questions of the kinds of data collected and specifically not collected to address customer concerns about what is being collected, and we hope to make that far more visible to customers in subsequent updates so that you can evaluate what data is being sent more easily. Microsoft has been able to find and fix significantly more bugs in SQL Azure than in earlier versions of SQL Server by using usage data more proactively to identify and fix problems, often before customers even know they have a problem. For example, we have been able to tell customers that they have performance problems or corrupt indexes proactively in our service, and customers have been extremely happy when we tell them about problems proactively. We’ve also found and fixed bugs that otherwise would require CSS cases to get fixes, costing customers time and effort to get a patch from Microsoft. We are hopeful that we will be able to more proactively help our customers have a great experience on SQL Server 2016 and beyond with this updated policy for customers who send usage data.

    I hope this clarifies some of the confusion on this thread. We will continue to work on improving the documentation to help inform customers about what kinds of data is collected and how it is used to make sure that each customer can make informed choices about whether they wish to enable or disable telemetry collection.

    Reply
    • Conor, thank you for the clarification. Very informative. One question: you mention that “The capability to disable [usage data collection/reporting] can not be turned off in free editions”. Specifically, what editions are you talking about? I assume SQL Server Express, but will Developer be included in this collection?

      Reply
      • Conor Cunningham, Architect, SQL Team
        June 7, 2016 3:51 pm

        Developer edition is free as of SQL Server 2016 (and therefore does not allow disabling usage data collection). The decision to make it free was made because we wanted to make it easier for everyone to get Developer edition and adopt SQL Server 2016 based on feedback our customers have given in past releases – it was actually unrelated to usage data collection.

        Reply
    • Conor – great point, there’s actually 3 separate issues:

      Issue #1: The EULA says updates will be forced. I recognize that that isn’t what setup is doing today, and I’m totally okay with what setup is doing today. However, the EULA gives much higher permissions, and that’s what I’m pointing out in the post. (It doesn’t bother me at all since I’m a fan of doing updates anyway.)

      Issue #2: All editions phone home by default. I’m fine with that too – I like telemetry to make the product better.

      Issue #3: Developer, Express, and Evaluation don’t have an opt-out option. I have a huge problem with that because developers need security too. In a perfect world, developers wouldn’t have access to client data. This isn’t a perfect world.

      Reply
      • Conor Cunningham, Architect, SQL Team
        June 9, 2016 9:29 am

        Regarding Issue #1 (“forced” updates): I am still double-checking the various EULAs (you pasted from the RC EULA, not the RTM one). The purpose of this statement, I believe, is that some components are shared independent of how many instances you install – for example, the client drivers. Installing a new version of SQL can update those binaries. They are backwards-compatible, generally, but installation does upgrade those bits from other versions if you install multiple versions on the same machine. I believe your concerns are hypothetical – we are not forcing customers to upgrade major versions of SQL Server (which makes no sense since Microsoft generally charges for each major version for you to get it).

        Regarding Issue #3: Nothing sent in the usage data includes customer data. The KB article specifically states this, and I’m working to review all the public content to make sure that is specifically stated in each place where it matters. If you have a specific compliance concern, please state it. We have very similar telemetry mechanisms in SQL Azure and have achieved numerous compliance certifications under full audit of the kinds of usage data being collected. There are specific processes to review usage data to enforce that property.

        Please note that, like in prior versions of SQL Server, if you agree to send crash dumps to Microsoft, that can include customer data since it’s just reading the memory of the process. We do not use the crash dumps to look at customer data purposefully – just to fix any bugs we find in the product as a result.

        I believe you are mixing “security” as a concern with the various vectors that concern you. If you don’t trust your developers with client production data, that has nothing to do with whether there is feature usage collection happening. Your problem in that case is that you don’t trust your developers. That problem exists in any database engine. I will note that SQL 2016 _does_ contain features that help you deal with better separation of duties issues like this. We have a feature called Always Encrypted which allows cyphertext-only in the DB for certain scenarios, meaning that the DBA (or a developer for the back-end) would not see the unencrypted data at all for key information. I’ll submit that SQL 2016 makes it possible to build _more_ secure applications in scenarios like this, not less.

        Reply
        • Conor – about #3: the problem is that without knowing what gets sent, I don’t know whether it includes customer data. Classic example: in the e-discovery business, I have clients who use their own customer names and legal case names as database names, table names, and index names. If database names, table names, or index names are sent back – let alone queries running during crash dumps – then they’re going to be really upset.

          About developer trust – these clients *do* trust their developers with that production data because the developers have signed NDAs and legal agreements. If MS is taking that data upstream, the customers don’t have NDAs or legal agreements signed with MS.

          Reply
  • Dave Ballantyne
    June 8, 2016 2:36 am

    This is an interesting one, im torn, and Conor does make some very good points, how does Microsoft really know that there development efforts are worth it ? Ive long said that the, as i see it, minor improvements ( ie XML and now JSON) get to much focus, hopefully now that this information is being gathered then better informed choices can be made. I could be wrong about that but at least the dev team will have metrics to back up the choices they make. Listening to Ken Van Hyning talk a few weeks back, his team are heavily guided by the telemetry input.

    With regard to ‘free’, if Dev / Eval are acquired under an MSDN licence are they ‘free’ ? Bit legalese , but i felt i should make that point.

    With regard to what information is gathered, having taken a *very* brief look at it , there is a CEIP service that connects to SQL with the SQLTELEMETRY$2016 user. Presumably it would break the licence to DENY this user access ( again , im not a layer) , but as it is a user they that is a technical possibility. But, what it does do is allow us to monitor what *is* being collected ( like is AdventureWorks and what version is installed ). It does also seem to have only a public level of access too, so presumably, if best practice is followed and PII , CC data etc is secured behind AD groups etc then it cant access it in the same way that any other user cant access it.

    Conor mentions a work in progress document, it would be nice to see what *could* potentially be collected over looking at a profiler trace ( I know what about Extended Events 🙂 ) to see what is.

    If i would suggest a ‘fix’ here it would be to have the CEIP service access to the data only via a series of views held in a ‘Telemetry’ schema so that the security can be monitored and controlled.

    Overall im broadly supportive of this move , but more openness and control to appease security concerns would be nice.

    Reply
    • Conor Cunningham, Architect, SQL Team
      June 14, 2016 8:13 am

      We’re working on some additional documentation and such to hopefully address your questions/concerns. It might take a bit for us to get them ready for release, but we will let you all know as soon as things are ready.

      Reply
  • There’s a CEIP Service installed with 2016, can you not just disable that?

    Reply
    • Simon – as long as you assume that it’ll never get enabled again by a SP or CU.

      Reply
      • Thanks Brent, very true. But I guess if they can alter a service from disabled to enabled (through a registry alteration presumably) then they can change a registry bit from 0 back to 1 ?

        Reply
        • I’m not sure what your question is? They’re Microsoft.

          Reply
          • Sorry about that. So what I meant is that where you said I could disable the service as long as ‘I assume that it’ll never get enabled again by a SP or CU’…the same assumption must be applied to the other workaround ie changing the registry entry for customer feedback from 1 to 0

  • Drew Fabbro
    June 15, 2016 5:32 pm

    “I bet in the days of phone app telemetry, folks are okay with it.”

    I guarantee there are a lot of people who don’t want their “SQL Express instance bundled in our app deployed on customer premises” people who don’t want their DB instance trying to talk to the Internet without their/their customer’s consent.

    Maybe simpler than a registry hack is to extend the standard solution for dealing with overly-Internet-eager apps. In the server’s hosts file:

    127.0.0.1 whatever-hostname-mssql-cep-is-trying-to-talk-to.com

    Reply
    • Drew – yeah, because they’d never think to use an IP address. 😉

      Reply
      • Certainly possible but many companies do not. For example, Adobe does not burn IPs for their activation servers into their Creative Suite. Of course, if you block them, your software can’t activate, but in this case, there’s no activation – you’re just blocking phone home.

        Of course, if it is an IP…well, we have firewalls 🙂 Also, if the IP is hardcoded, word will spread quickly and those IPs will quickly become known and easy to block.

        Reply
        • It’s worth mentioning that for some Microsoft phone-homes, you cannot override addresses using the host file because it ignores overrides for those addresses.

          Reply
      • Actually, is very possible they doesn’t have an IP address. An customer had problems because their company policy does not allowed to create firewall port rule based on DNS, only on IP addresses.
        It was because the vendor had no fixed IP and they have to point to the name of the server for their app-store-like license server.
        Seems IP addresses are an scarce resource nowadays.

        Reply
  • The CEIP Service will be there/installed by default on SQL 2016, is that true?
    is there any way to install SQL 2016 (Standard edition) without the CEIP Service? maybe with command line/unatenned installation?
    This service is only for telemetry and do not have any other function, correct?
    Thanks a lot

    Reply
    • Andi – how are we supposed to know?

      Reply
      • Conor Cunningham, Architect, SQL Team
        June 22, 2016 4:26 pm

        SQL CEIP is only used for usage data collection. It is not used for any other purpose.

        Andi, the SQLCEIP service is installed with all SKUs. It is installed even when you do command-line installations. However, you can disable usage data collection using the documented methods from the referenced KB article (meaning the SQLCEIP service does nothing in those cases). We do not document methods to remove the SQLCEIP service itself since it may break your servicing patches when you try to install them later.

        Reply
        • Hello Conor,

          Regarding “We do not document methods to remove the SQLCEIP service itself since it may break your servicing patches when you try to install them later.” ….

          does this also include not recommending to disable the SQLCEIP service ?

          Reply
          • Conor Cunningham [MSFT]
            November 7, 2016 5:59 am

            The KB article discusses what is supported here:
            https://support.microsoft.com/en-us/kb/3153756

            In short, the SQLCEIP service is not supported running “off”. Over time, we plan to add other features for you tied to this part of the code, so turning it off will have consequences beyond what you are expecting. We also don’t test in that configuration and can’t recommend customers run in this fashion (it might break upgrades)

  • I am a bit late in this conversation, however I only realised the existence of this telemetry service when I installed my first clustered instance of 2016. To my surprise a new clustered resource appeared “SQL Server CEIP ()”. I haven’t found any explicit documentation regarding this as a clustered resource. As non-critical service I am happy to see that the default install leaves the clustered resource Police check box “If restart is unsuccessful, fail over all resources in this role” unchecked.

    Reply
  • Conor – any update?

    You might want to update the KB. It appears 3153756 might already have been updated (per its “Last Review: 06/20/2016 22:41:00 – Revision: 3.0”). I suggest also updating https://connect.microsoft.com/SQLServer/feedback/details/2775205.

    This topic is a bit of a sticky wicket for my shop. I must know what usage reporting will and will not report. And I must be able to disable error reporting via a GPO for Developer Ed. Otherwise I must play after-the-fact whack-a-mole on our firewall, which won’t wash with our Security team. Only after this topic is satisfactorily addressed will I be able to move ~150 developers to 2016 (and upgrade ~100 cores to 2016 EE).

    Reply
  • Conor Cunningham, Architect, SQL Team
    July 14, 2016 2:45 pm

    We updated the KB with information about how to use the UI to control what data is sent via the usage mechanism.

    We are actively working on releasing more capabilities here to make this more transparent for customers about what is collected, but some of these things require writing new code and dealing with layers of reviews through the company. Please feel free to engage with us for any specific issues that are blocking you in the meantime. We can often answer enough questions to unblock a specific customer while we work on the official extensions we plan to release.

    We are not planning to allow disabling usage data reporting on developer edition, but we are happy to demonstrate that what we are collecting is benign in those editions.

    Reply
  • As a lifelong advocate for MS, I hope this will reach some execs at MS and invoke serious thoughts. Assume for a moment that I represent a large community of long-term MS technologists who absolutely LOVE the long history of MS accomplishments and innovations but believe the following realities:

    1) Technology has become a commodity. Most companies don’t care about which technology drives a particular app, just that it works, delivers the required features to users on various devices and is SAFE (including growing concerns for privacy). Which database engine is humming in the background, which UI framework is rendered thru or which language/platform are used in the middle-ware, are largely irrelevant now.

    2) Technology market has become over mature. There are plenty of choices in today’s database marketplace and let’s be honest: most apps don’t need more than the basic database capabilities available in almost all current database technologies. Many of these options are open-source, free and without any of the deceptive privacy intrusions like mandatory/default data collection.

    3) MS seems to have lost its “developer is king” soul. Developers (coders, designers, architects, etc) advise our companies and clients on technology decisions, particularly in the environment outline in #1 and #2 above. MS must consider how comfortable we and our customers FEEL with the capabilities and safety/privacy of a product line or platform. Any data collection or phone home in a database engine is flat our OUTRAGEOUS, considering the bulk access to sensitive assets like business data and IP of the codebase. If anything, MS can distinguish itself in this new marketplace by fully embracing and championing our growing needs for security and privacy.

    4) MS missed the boat on security and then there was Bill Gates’ famous memo on security over a decade ago. It seems MS has become very careless and deceptive in regard to the market’s demands for privacy. Disclosure alone is NOT sufficient and again misses the boat! Reliable, efficient and verifiable control is key. Your customers must have clear, efficient and verifiable means to control the privacy settings of your products.

    5) We are tired of this heavy-handed manipulation of our tools and work environment by MS. To treat competent and thoughtful developers and system admins and db admins as mind-less lemmings whose work habits have to be recorded, inspected and monitored through excessive data collection (for our own good) is just insulting. There is a canyon of concern between Opt-in and Opt-out. This transition cannot be made without serious justifiable benefits for the users.

    6) A free product contaminated with phone-home “features” is not free anymore! SQL Express have led to a large adoption of MS SQL and growth in revenue from system installations (or if nothing else Developer bleed away from MS SQL to other platforms). Free product editions to benefit MS immensely in the past decade. It is not acceptable to force free product users with parting with their fundamental privacy interests. That’s not a fair trade!

    7) As historically with other trends, MS missed the trends of simplistic consumer-grade computing (Apple) and cloud data advertisement syndication (google). Now in a zealous, top-down corporate “run of the bulls” rampage they are contaminating even their professional product lines with over-simplistic user interfaces (Win 8/10 Pro) and aggressive/deceptive data collection practices (almost EVERY product line by now). This will backfire in a dramatic way. You may not posses the marketing sensors to feel these market reactions now (although the negative fallout from excessive Win 10 telemetry has been widely reported). But these are pre-shock tremors which will reach critical mass and the resulting market calibration will hit MS bottom-line hard.

    To summarize in one simple canon:

    The cynical or even legitimate needs of MS to collect information about my work/production environment cannot ever supersede my real or even perceived needs for privacy!!! Period…

    If this trend does not reverse quickly, professional developers will accelerate their migration to Linux and open-source, which are quickly becoming the new norm of “professional”, grown-up computing. Microsoft will be left a shadow of their former glory and only catering to minimal corporate legacy fringes. You mess with DB technologies especially and the fallout will be too severe to control. see long list of Clipper, DBase, Sybase, Oracle for reference.

    Please think and learn…

    Reply
  • Our corporate council has determined that SQL 2016 will not be allowed to be deployed because of the automated updates “feature” and higher risks generated by Microsofts heavy handed tactics. We were a MS shop with over 75 SQL instances with 2 vendor supplied MySQL servers. We are now hiring developers and admins with Linux experience. Moving projects from IIS/MSSQL to Apache or Nginx with with Postgres or Casandra.

    Reply
    • Conor Cunningham [MSFT]
      November 16, 2016 2:28 pm

      We’re happy to talk to you and answer any questions you have about your future data platform investments. This particular blog post is misleading and isn’t the forum to ask questions to Microsoft about SQL. Also, please note that usage data can be disabled in paid versions of the product.

      Reply
      • Conor – hey, if there’s anything misleading in here, let me know. I’d definitely want to correct it. (Not being sarcastic here.)

        Reply
        • Conor Cunningham, Architect SQL Team
          January 4, 2017 4:13 pm

          Brent, your blog post picks and chooses passages from the SQL Server EULA and Privacy Statements and then construes behavior of Microsoft or possible behavior of Microsoft that is not a reflection of what actually happens. In some cases, we need to go be more specific in the privacy statement or the EULA to avoid such interpretations. In other cases, we need to add more features (like we did by adding an ability to audit all the data being sent to Microsoft through the normal usage reporting mechanism in CU2, I believe).

          As to whether your blog content is correct in substance (ignoring tone), the portion of the EULA that you reference related to automatic updates has to do with cross-version components that are intended to be backwards-compatible across versions, such as the ODBC drivers. So, installing SQL Server 2016 might update your ODBC driver to the latest-and-greatest with all the bug fixes we’ve done for it.

          We have been using the usage and crash data from SQL Server 2016 to proactively fix product bugs at a MUCH more aggressive rate than in prior releases. While we still have a ways to go to make sure that our customers can see this for themselves in a transparent enough manner that everyone can decide for themselves to trust Microsoft with this usage data or not for a production system, I can say that this model has yielded significantly better software for our customers than waiting for each customer to hit a production issue and THEN talk to our support team.

          Please look for more from us as we complete additional tasks to help customers see what we are doing here and better understand the value it delivers to them.

          Reply
          • Conor – if the EULA doesn’t reflect what MS does, change the EULA.

            This reminds me so much of contract discussions that I have with customers. A prospective customer’s contract will say something like, “We now own all your IP.” When pointed out, they say things like, “Well, we would never actually exercise that clause, of course.” Great – then take it out. Otherwise, the contract is the contract, and it gives Microsoft the power to do some pretty heinous stuff right now.

          • Meanwhile, since SQL 2000, we still get the hidden MODAL dialog that “activity monitor” has failed. and my SSMS is dead til i start ALT-TABBING.

            but please, get upset about someone pointing out some stupidity.

            That someone being Brent.

      • The No -Sql 2016 directive came from the executive level. So the IT managers are trying to figure out where to go. From the job posting I am seeing, most DBA work is now for Oracle, MySQL, various No-SQL database engines. Very few are looking for MS-Sql admins and even less for MS-Sql developers. Yes I am looking for another job, because we will not have any MS-Sql server by Q3-17.

        Reply
  • You can also change the the phone home settings using the ‘Error and Usage Report Settings’ tool.

    Reply
  • Conor Cunningham, Architect SQL Team
    January 4, 2017 4:31 pm

    Brent, changing the EULAs for an in-market product is non-trivial since it impacts many, many customers. Often we are not allowed to change that once released into the market – period. However, we are working to try to clarify various parts of our public documentation and other artifacts to help here. We will be happy to discuss with any customer details of what we do and don’t do to correct any misinformation, but calling what we do “heinous” is perhaps the most negative interpretation of some generic language in a document that we will work to improve/clarify as we can. It appears to me that you are using this blog post more to register your own displeasure over the policy change rather than to post factual details of what happens in the product today.

    If you are interested in what the SQL team actually does instead of just trying to tell us what you want us to do, then please feel free to do a blog post on the telemetry we emit since you can go review it for the core engine. We fast-tracked that work to help our customers see for themselves that the data being collected is generic usage data to help us make sure that features are working or that we fix common bugs proactively.

    While you are welcome to your own opinion about the change in SQL Server 2016, my advice to Microsoft SQL Server customers is to talk to Microsoft directly to avoid misinformation and mischaracterizations. Generally speaking, when we explain to customers what we collect and how we use it, they are pretty happy that we are more aggressively improving the product for them without having to open support tickets. In environments where regulatory issues exist, it’s easy enough to disable such that those customers can disable the feature and solve their problem with minimal effort. We are happy to assist any customers with questions, but I can’t do anything more except tell customers that this blog is not the right forum for this discussion. Please refer to official Microsoft documentation instead.

    Reply
    • Conor – by talking to Microsoft directly, do you mean filing a Connect item? Because I did that six months ago (and linked to it in the post), and despite hundreds of upvotes on the item, and lots of questions from customers on it, there has not been one single note from MS there.

      Not. One. Sentence.

      This blog post, however, has generated lots of discussion with Microsoft employees directly, including your comments.

      So I ask you, which one am I supposed to use?

      Reply
      • Conor Cunningham, Architect SQL Team
        January 4, 2017 4:55 pm

        No – Please engage with your sales contacts in the Microsoft field team, open a support case, or reach out to us directly about a sale issue impacted by this. Connect items are for opening bugs (which we do review and fix but generally for future releases since you didn’t open a support case) or for making “suggestions” (which we review periodically but do not ever close anymore – we don’t necessarily reply to each and every one because there are many, many suggestions and sometimes they are things that we aren’t going to do such as “give away your product for free” or similar). You have likely opened a suggestion. We’re not going to do what you suggest at this time because we made a policy change and we’re using that data to improve the product (as I explained here in other replies). You know this because we told this to you and the other MVPs in the MVP forums before we released the product. If you want to hang your hat on whether we replied to the suggestion on connect as implying that we’re completely ignoring this issue, I will go make sure that we tell you that in your connect item since you don’t think my word here is “official” enough when I reply on your blog post.

        Reply
        • Thanks, I’d appreciate that.

          Microsoft has indeed reversed course in the very recent past – the 2016 SP1 licensing/feature changes come to mind – so I maintain a stubborn optimism that things can get better. 😀 Thanks sir!

          Reply
  • It does seem to me that if the EULA says something that generates concerns and the fix for the concern is to contact an agent of some sort who will tell you what they are actually doing that it is less onerous than what the EULA indicates then the EULA is a big problem. No lawyer worth his BAR exam results would let you substitute agent assurances for a signed contract (or EULA in this case).

    I’m sorry, but assurances they Microsoft is doing this for the best of reasons is not an actual change from real legal position as laid out in the EULA. And actions such as the prevention of opt outs for any version is scary for folks who work for organizations who do have security concerns (and lawyers).

    The shift in culture at Microsoft under the cloud regime is adversarial for no reason other than making it more difficult for businesses to run outside Azure. Microsoft’s new overlords want subscription fees not sales revenue and EULAs and development plans for all products are oriented toward pushing customers with purchased systems toward monthly services.

    But what do I know, I have been frustrated by these types of moves in Microsoft Dynamics-land for a while. I may just be overly sensitive.

    Reply
  • [[The shift in culture at Microsoft under the cloud regime is adversarial for no reason other than making it more difficult for businesses to run outside Azure]]

    Agreed, and it is not only enterprise software. They want to go to a subscription model for everything. Everyone pays ongoing and forever to use anything from Microshaft…

    Reply
  • Lars-Petter Larsson
    May 2, 2017 3:49 am

    I think the registry path is wrong above, it is not within HKEY_CURRENT_USER but HKEY_LOCAL_MACHINE

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}