Lesson Learned #130: Does MARS work in Azure SQL Managed Instance?

by Scott Muniz | Jun 19, 2020 | Uncategorized

This article is contributed. See the original author and article here.

Today, we worked on a service request where our customer faced the following error message: “There is already an open DataReader associated with this Command which must be closed first” performing a SELECT operation and INSERT using the same connection.  Our customer asks about the availability to use MARS in Azure SQL Managed Instance. We provided an example how it works also in Azure SQL Managed Instance. 

1) As you know we enable MARS using the following connection string: data source=tcp:servername.virtualclustername.database.windows.net;initial catalog=DataseName;User ID=username;Password=Password;ConnectRetryCount=3;ConnectRetryInterval=10;Connection Timeout=30;Max Pool Size=100;MultipleActiveResultSets=true

2) We developed the following C# Code:

        public void Inicia(int nRows, bool bPooling, bool bInstanciaCadaVez = false)
        {
            try
            {
                Stopwatch stopWatch = new Stopwatch();
                stopWatch.Start();
                C.SqlConnection oConn = new C.SqlConnection();
                ClsRetryLogic oClsRetry = new ClsRetryLogic();
                if (oClsRetry.HazUnaConexionConReintentos(GetConnectionString(bPooling), oConn, bInstanciaCadaVez))
                {

                    C.SqlCommand command = new C.SqlCommand("SELECT count(Id) FROM PerformanceVarcharNVarchar Where TextToSearch = @Name", oConn);
                    command.CommandTimeout = 1200;
                    command.Parameters.Add("@Name", SqlDbType.VarChar, 200);
                    command.Prepare();

                    C.SqlCommand command2 = new C.SqlCommand("INSERT INTO Table_1 values(@Name)", oConn);
                    command2.CommandTimeout = 1200;
                    command2.Parameters.Add("@Name", SqlDbType.VarChar, 200);
                    command2.Prepare();
                    
                    Random rnd = new Random();
                    for (int tries = 1; tries <= nRows; tries++)
                    {
                        Console.WriteLine("Execution Nr.: " + tries.ToString());
                        Console.WriteLine();
                        command.Parameters["@Name"].Value = "Example " + rnd.Next(1, 450338).ToString();
                        command2.Parameters["@Name"].Value = rnd.Next(1, 450338);
                        C.SqlDataReader SqlReaderC = command.ExecuteReader();
                        while (SqlReaderC.Read())
                        {
                            Console.WriteLine("Valor {0}", SqlReaderC.GetValue(0));
                            command2.ExecuteNonQuery();
                        }
                        SqlReaderC.Close();



                    }
                }
                oConn.Close();
                stopWatch.Stop();
                TimeSpan ts = stopWatch.Elapsed;
                string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                    ts.Hours, ts.Minutes, ts.Seconds,
                    ts.Milliseconds / 10);
                Console.WriteLine("Object type:{0}. Time: {1}", bPooling ? "Pooling" : "without Pooling", elapsedTime);
                Console.ReadLine();
            }
            catch (Exception e)
            {
                Console.WriteLine("Ups!! " + e.Message);
                Console.ReadLine();
            }

        }

Finally, we could see that both process are executing in the same connection. 

Enjoy!!!

How Azure Sphere prevents rollback attacks

by Scott Muniz | Jun 19, 2020 | Uncategorized

This article is contributed. See the original author and article here.

A rollback attack is a common type of security attack in which an attacker tricks the target into running an older, less secure version of its software. Once the insecure software is running, the attacker can take advantage of its vulnerabilities. With thousands of field-deployed devices, Azure Sphere represents a likely target for such an attack. Azure Sphere uses two main strategies to prevent such attacks: one-time programmable (OTP) fuses and attestation.

One-time programmable fuses

OTP fuses can serve as circuit breakers that are built into the hardware. On Azure Sphere, the Pluton security subsystem contains several such fuses, which use the e-fuse technology. To stop trusting (and running) all previous versions of the OS, Azure Sphere “blows” an e-fuse upon update to a particular release. During OS installation, the bootloader compares the number of blown e-fuses on the device to the number expected for the OS being installed. If more e-fuses are blown than the software expects, the software must be too old, so installation fails, and the last known good version of the OS is run instead. The number of e-fuses available in Pluton is limited. Therefore, we blow them only when necessary.

Attestation

Attestation requires that every Azure Sphere device prove every 24 hours that it is running valid, trusted software and is an authentic Azure Sphere device. When a device connects to the Azure Sphere Security Service, it provides an attestation value that combines a hash value from each loaded binary and additional data from the device with a nonce from the Security Service. The attestation value is signed using a device-specific private ECC attestation key that was generated when the chip was manufactured. The key is stored in Pluton, is used exclusively for attestation, and is inaccessible to any software. The Security Service has the corresponding public key, so it can verify that the device is authentic. The Security Service uses the binary hashes and additional data to verify that the software is trusted.

If attestation succeeds and the software is trusted, the device receives the certificates required to connect to the Azure Sphere update service and to authenticate as a client to other services. If the attestation value is invalid or has an incorrect signature, the device fails attestation outright and does not receive any certificates. It can neither connect to the update service nor authenticate to another web service.

In some cases, however, the Security Service may determine that the binaries are valid but are not trusted. This check guards against rollback attacks because it prevents an attacker from loading an older OS that posts a security risk. For example, if a binary contains a zero-day exploit, Microsoft might decide not to trust it as part of the attestation process. In this situation, the device is not granted a client certificate, so it cannot connect to web services. Instead, it receives only an update certificate so that it can update to a trusted version of the software. Once the update is complete, the device restarts the attestation process. If the binaries are now both valid and trusted, the device receives both the update and client certificates and can operate as usual.

After 24 hours, the certificates expire, and the device must repeat the attestation process.

Strong, flexible combination

The combination of e-fuses and attestation constitutes a strong yet flexible range of protection against rollback attacks. Attestation using secure ECC keys and binary hashes provides a way to remove trust for a single binary image in an OS release, a single OS release, or a range of releases in such a way that the device cannot run untrusted software and can easily update to a newer, trusted version. Blowing an e-fuse provides the ability to remove support for all releases earlier than a particular version at the hardware level, ensuring that the device cannot run older software.   

For more information about security features in Azure Sphere, see Best practices for implementing seven properties in Azure Sphere.

Learn with Creators: Excel Highlights

by Scott Muniz | Jun 19, 2020 | Uncategorized

This article is contributed. See the original author and article here.

As you probably already know, YouTube is filled with some incredible creators who are out there helping people make the most out of Excel. Some of those creators are part of the Microsoft Creators Program, and we thought you might like to see a little of what they’ve been sharing!

Below are some highlights from this week, focused on the new STOCKHISTORY function—if you find these helpful, be sure to check out their channels!

See more from Faraz Shaikh

See more from Chris Menard

See more from MyOnlineTrainingHub