Tag: Microsoft

Azure Stream Analytics to SQL Server – Isn’t that simple? or May be not..!

It is quite a known pattern to use Azure Stream Analytics (ASA) to create data pipelines to store ingress IoT data to an output location be it a SQL Server or EventHub or Azure Storage etc. This is all the more important when push ASA to the edge and use that as a data transformation and storage orchestration engine esp. with SQL Server on local on premises being used like a Historian. This seems to a straight-forward process but here comes a small catch and technical/product limitation of Azure. It took us a few precious hours to understand this aspect.

Short Answer (if you have scrolling like me):

“If you want to connect ASA with SQL server, ensure that you have a trusted CA certificate with proper certificate chain installed in the SQL server VM”.

For the patient ones who need the backstory šŸ™‚ read along..

What were we doing?

We were trying to wire up an Azure IoT edge module with a SQL server on a VM! This seemed quite easy as per the documentation but I ended up with a curious certificate error.

As a troubleshooting step, I tried to create this on ASA on cloud and connect with the same SQL server on VM to rule out any Edge VM certificate issues. This should be quite quite simple if we follow this blog.

https://docs.microsoft.com/en-us/azure/stream-analytics/sql-database-output

No big deal. So you thought.

But, I still got the famous certificate chain error.

So I started doing the below documented steps

Using Encryption Without Validation – SQL Server Native Client | Microsoft Docs

  1. Set Force Protocol Encryption Client Setting to Yes
  2. Ā For secure connectivity, ensure that the client and server both require encryption. Also ensure that the server has a verifiable certificate, and that theĀ TrustServerCertificateĀ setting on the client is set to FALSE.

Created self signed CA certificates and installed them as well. But then still the issue seems to be coming back and back.

Solution

Finally, we found out from Microsoft product team that we need proper CA certificates with certificate chain from well known authority to make ASA and SQL work together.

One requirement for SQL server on VM as output to work is that the SQL server needs to be configured with an SSL certificate issued by a trusted CA. There is no workaround with this. You can’t use a self signed certificate or use TrustServerCertificate=True and change SQL Server settings.

1- Regarding SSL Certificate – Make sure to use the DNS based FQDN for the CN. Here are the full requirements listed.

2- SSL Setup in the VM – Follow steps here. If using SQL 2016 , Also put the certificateā€™s thumbprint in the registry key mentioned in the ā€œWildcard Certificatesā€ section. 

Now for me who is just doing a dev setup and doesn’t have the luxury of client CA certificates, there are quite limited options.

For IoT Edge, I used a custom .NET code with SQL DB client to communicate with SQL server VM using the TrustSeverCertificate = True flag in connection string for dev code until I get a CA cert.

But for Azure Stream Analytics PaaS service, we can’t enter connection string. So there is no way to enter TrustSeverCertificate=TRUE during development. Sure seems like a restriction.

One another way which is to use services like Let’s Encrypt and generate a chained certificate for your use temporarily. Something which I am yet to try. I think that should work.

If you have used that and worked, please let me know in the comments.

Back to Sensing, Streaming and Storing..

Advertisement

Exporting SharePoint List template from one Microsoft Teams to another!

Happy 2020!

What I am going to document here might be straightforward for certain people but wasn’t obvious to me when I started doing a very silly List template copy in MS Teams. Hence this post!

As part of my work, I am in a Microsoft Teams, let me call it Team 1, where I have a SharePoint List, let me call it TrackerList, added as a tab. This list helps our team add certain requests with their details and attachments.

As you might be already aware, it is a simple task to add a new List as a tab – Link

SharePointList1

Now, comes the interesting bit. I had to manage the same task in another Team, let us call it Team 2, where this same TrackerList’s template was needed. Since, I was not a SharePoint / Teams regular user, I was in a spot.

So, as usual, I started analysing the options.

OPTION 1: I started with this link from techcommunity.

  1. Go to the underlying SharePoint site for Teams in the Teams where my Original Tracker List was there.
    SharePointList2
  2. You will be taken to your SharePoint that drives your Teams. on the Site Navigation pane on the left side you can see your TrackerList file.
  3. Click on that to Open your SharePoint List, click on Export to Excel and save the query.iqy file.
  4. Open the file and Enable the data concern in the security pop up window as long as it is allowed in your machine (some firewalls and companies don’t allow this) and source of file is vetted.
  5. Now, you have the excel sheet with the data from SharePoint.
  6. Go to Table->Design-> Export the table to a SharePoint List.
    SharePointList3
    SharePointList4
  7. I gave the Link to my SharePoint that drives Teams2.
  8. I was able to use the Next and Finish to publish it with a New Unique name to that end point (provided I had access)
  9. Go to Team2’s SharePoint backend.
  10. Now, you can’t see that it in the site Navigation pane
  11. But you can see it in the Site Contents.
  12. You can try adding it to Site Navigation Pane using Edit.
  13. But after all this, you can’t see it in the below flow when you want to add this List as a tab to your Teams 2.
    SharePointList5
  14. Now, I don’t know why this happens. May be there is some meta which is needed. if you create a new List from this SharePoint base link, then it appears on Site Navigation by default and then comes in Add List as Tab workflow as well!

OPTION 2: Let me try create a new List from the import the freshly created List in the above option 1

How to achieve this?

  1. Create a New List Item but then I wasn’t able to copy it from a template!
  2. Dead End

Start over:

  1. Go to Site Contents instead
  2. Create a New List
  3. you get a new Window now where you are by default in your own Site and can see the different Lists created or imported from Option 1.
  4. you can choose to create a list from the previously exported Lists!
    SharePointList6
  5. Now go back and try to add this list in Teams 2.
  6. Oops. Doesn’t appear still

OPTION 3: Let me try now using the imported list in the Site Contents and somehow create a new List.

Instead the steps are actually too simple!

  1. Go to Site Contents instead
  2. Create a New List
  3. you get a new Window now where you are by default in your own Site and can see the different Lists created or imported from Option 1.
  4. you can choose to create a list from this – BUT DON’T as we saw in option 2 that doesn’t work
  5. you should scroll below to the other Teams, Team1 and the specific list you need will be there.
    SharePointList7
  6. Select whichever lists you need and click Create.
  7. Done – you will have the same template without the data created for you!

And, I am happy saving 2 hours copying templates and recreating them.

Now you may ask me a question what if my sample template and SharePoint List is in another Tenant!

I don’t have an answer for that yet. I somehow think Option 1 and Option 2 together should solve it, with some support from Microsoft end to say why MS Teams, Add SharePoint List is not showing the List added in that fashion. May be some metadata is missing when added like that.

Hope this helps!

Yours Half-brained,
Trilok R