Wednesday, September 13, 2017

Finding Stuff in SQL Server

One of the many hats I wear as a software consultant is that of database developer. Part of that entails the discovery of where data is stored, or how it integrates with existing software systems.

For example, suppose you need to find any reference to social security number in your client's database. You'd need to find references to variations of that, like "SSN" or "Social" in tables, views, stored procedures, and user-defined functions.

So, I created these templates in the Template Browser for SQL Server Management Studio, just to accelerate this process. The queries use system views to find SQL Server objects based on LIKE statements.

Find text in table columns

SELECT ss.[name] + '.' + st.[Name], sc.[Name] 
FROM sys.columns AS sc
INNER JOIN sys.tables AS st ON st.object_id = sc.object_id
INNER JOIN sys.schemas AS ss ON ss.schema_id = st.schema_id
WHERE sc.[Name] LIKE '%SSN%'
ORDER BY ss.[name], st.[Name], sc.[Name]

Find text in views

SELECT ss.[Name] + '.' + sv.[Name] 
FROM sys.views AS sv 
INNER JOIN sys.schemas AS ss ON ss.schema_id = sv.schema_id
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%SSN%'
ORDER BY ss.[Name], sv.[Name]

Find text in stored procedures

SELECT ss.[Name] + '.' + sp.[Name]
FROM sys.procedures AS sp
INNER JOIN sys.schemas AS ss ON ss.schema_id = sp.schema_id
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%SSN%'
ORDER BY ss.[Name], sp.[Name]

Find text in user-defined functions

SELECT ss.[Name] + '.' + so.[Name]
FROM sys.objects AS so
INNER JOIN sys.schemas AS ss ON ss.schema_id = so.schema_id
WHERE   so.type IN ('FN', 'IF', 'TF')
AND     OBJECT_DEFINITION(OBJECT_ID) LIKE '%SSN%'
ORDER BY ss.[Name], so.[Name]


Thursday, September 29, 2016

Issues with IIS after Windows 10 Anniversary Update


After the Windows 10 Anniversary update was automatically installed, I noticed none of my local websites were working – instead giving me a 503 – Service Unavailable. Being a developer, and one that relies quite heavily upon IIS, this was deeply troubling.

Luckily, I saw this before after my Windows 7 to Windows 10 upgrade. If you see this issue, you may have experienced something similar.

Check IIS


First, I did the typical restart stuff, with the command-line IIS reset, and tried hitting the local website again, with the same result. In IIS, the application pool for the Default website had stopped.

Check Logs


Next, I looked in Event Viewer. In the Application Windows Logs, I saw this error:

The Module DLL C:\WINDOWS\system32\inetsrv\rewrite.dll failed to load.  The data is the error.

Once again, there was an issue with an IIS module – in this case, the URL Rewrite module.

Repair or Reinstall


In Control Panel –> Programs and Features, you can find the “IIS URL Rewrite Module 2” in the list of installed programs. You can either repair the module here, or uninstall and install again.
In my case, I chose the scorched earth approach, and uninstalled.

Now, you can re-install the module with Microsoft Web Platform Installer. A shortcut is conveniently located in the IIS manager, under Management. You can also find it in the Start menu. Search for “URL rewrite”, and the module will appear in the results. Select and install, then try again.

Try Again


I hit my local website, and voila! The website loaded successfully. (Note: You may have to reset IIS to take effect). Hope this helps.

Mark Doyle
Software Architect, Collabroscape
@mark_doyle_ftw

Tuesday, January 13, 2015

Configuring Azure Virtual Networks (Using New Azure Portal)

This is a follow-up to the article “Configuring Azure Virtual Networks”, which demonstrates using the previous Azure Management portal, http://manage.windowsazure.com.

Azure Virtual Networks provide a great way of allowing cloud services, websites, and virtual machines to communicate with each other, much like in an on-premise intranet. As a novice on the networking side, I had to learn some basics about these networks, and I'd like to pass on some of these lessons.

Summary

In this article, I will cover how to set up an Azure virtual network ("VNET"), how to set up an Azure virtual machine ("VM") on that VNET, and then how to configure that VM for web traffic, including endpoint and ACL configuration. As an added bonus, you'll see how to configure an Azure Web Role cloud service on the VNET, as well as how to restrict web traffic to only allow other VMs and services on the VNET.

Create the Azure Virtual Network

Obviously, since we are adding VMs to a VNET, we need to create both. To start, click New –> Everything, and then either find Virtual Network in the Virtual Machines gallery, or search for “Virtual Network”.

portal_01_create_vnet_step_1

Step 1: Specify VNET name and location

portal_02_create_vnet_step_2

Specify a meaningful name, then select the appropriate location. Here, I’ve specified “vnet-md-demo2”, and the South Central US location, since I’m closest to it.

Remember, you’ll get less latency if your storage accounts, services, VMs, and VNETs are in the same location (data center).

Step 2: Configure the address spaces, or IP ranges

portal_03_create_vnet_step_3

The address space configuration allows you to set up the IP ranges for the VNET, with multiple parent address spaces, each with multiple subnets. You need to specify the CIDR, but Azure provides the address count that each represents. You can do the same for the subnets, up to the number of addresses allocated in the subnet’s address space.

NOTE: If you are interested in learning more about CIDR: http://ip2cidr.com/cidr.php

For this article, I’ve created a single address space with 128 addresses, by changing the CIDR from 10.0.0.0/16 to 10.0.0.0/25. I’ve also created the first subnet, aptly named “Subnet-1”, that has 16 possible addresses, instead of the default CIDR 10.0.0.0/24. Since I’ll be creating 2 VMs, I will configure another subnet below. You can build your network as you see fit, and even across subnets, the machines will be able to communicate with each other. This also applies to cloud services, both Web Roles and Worker Roles.

Step 3: Browse to VNET for further configuration

portal_04_navigate_to_vnet

If you did not select the option to add the tile to your Startboard, you can browse to the new VNET by clicking Browse and looking under “Recent”, or click Everything to find the new VNET.

portal_05_vnet_info

Step 4: Configure additional subnets

Click the Subnets tile to configure the additional subnets.

portal_06_config_vnet_step_1

Click Add subnet to do so.

portal_07_config_vnet_step_2

Provide a meaningful name, choose your existing address space, and then specify the CIDR block. This will provision the additional subnet, which will still be able to communicate to other machines in the address space.

Create the Azure Virtual Machines

As mentioned above, we are going to create two VMs on our newly-created VNET, so we can demonstrate how they communicate with each other. To start, click New –> Everything, and then either find Windows Server in the Virtual Machines gallery, or search for “Windows Server”.

portal_08_create_vm_step_1

Step 1: Select the image

portal_09_create_vm_step_2

For this article, we’ll select Windows Server 2012 R2 Datacenter, which supports a wide variety of applications and web functionality. Plus it’s the data center edition, and that just sounds fun. Simply select that option from the Windows Server image list, and click Create.

Step 2: Provide VM name and credentials

portal_10_create_vm_step_3

Provide a meaningful name, but make sure it is DNS-compatible – use letters, numbers, and hyphens. For this article, I’ve chosen the VM name “vm-md-demo2a”.

Step 3: Select pricing tier

portal_11_create_vm_step_4

For this article, I’ve selected A1, since I can run it all month without going over my credit limit.

NOTE: Another great thing about VMs is that you only pay for what you use, meaning that if you shut the VM down, you don’t pay for it, even though the image still retains whatever you have installed. If you want to try out the beefy machines, like the A7, which has 8 cores and 56 GB RAM, you can run it for a few hours, then shut it down.

Step 4: Configure network

portal_12_create_vm_step_5

Under the Optional Configuration, select Network to select the virtual network and provide the Subnet. Be sure to select the VNET you previously created, then you’ll be able to select the desired subnet.

NOTE: The Azure portal will default to creating a VNET with the same name as your VM. Be sure to change it to the desired VNET.

Step 5: Select location

portal_13_create_vm_step_6

Finally, select the desired location. For this article, I’ve specified South Central US. In the previous version of the Azure Management portal, the VNET was already associated with a location, thus requiring you to select the location or the VNET. This new portal provides a bit more flexibility.

Step 6: Finish the wizard

Once you have configured the desired options, complete the wizard and Azure will begin provisioning the VM.

Step 7: Configure the second VM

Follow the same steps to create another VM, except on Subnet-2 (or whatever name you specified for the second subnet), and wait until both VMs have been provisioned.

Configure VM as a Web Server

So that I can test connectivity, I’m setting up the first VM as a web server, as this will likely be a common use for future Azure VMs you configure.

Step 1: Configure Windows

Connect to the VM with RDP, which you can do through the Azure management portal by clicking “Connect”, after you browse to the VM.

Setting up the web server entails adding the additional Windows features for IIS, and the necessary extensions for ASP.NET, etc. These specific steps are outside the scope of this article, but there is an abundance of information on how to do so.

Step 2: Create a test page

Let’s create a test web page we can hit from our other VM and from our local PCs. By default, you’ll create the default web page in C:\Inetpub\wwwroot\. You can test with http://localhost/. Here, you can see I’m rocking the classic ASP:

portal_14_create_test_page

Step 3: Try to browse to the web VM from your home PC

Open up a browser, and try to hit [your-vm-name].cloudapp.net. It probably looked like this:

portal_15_attempt_no_config

Why didn’t it work? We were able to connect via RDP...

Because we need to open up the appropriate endpoint!

Add an Endpoint to an Azure Virtual Machine

Endpoints are like the firewall. We need to configure which ports are open, and they even support port redirects, just like a router. To start, go to your VM in the Azure portal, and click Settings.

portal_16_add_endpoint_step_1

From there, select Endpoints to begin.

Step 1: Add and specify type of endpoint

portal_17_add_endpoint_step_2

Click the Add button, and configure the endpoint settings.

NOTE: Unlike the previous version of the Azure management portal, there are no pre-set values to choose from. You will need to input the endpoint name, select the protocol, and specify the public and private ports. Also, for non-typical ports like Elasticsearch (9200), you may also have to configure the Windows Firewall to open up that specific port.

Step 2: Try again from your PC

After the endpoint has been created, try browsing to the VM again, and you should succeed.

portal_18_success_after_endpoint

Adding a Cloud Service to an Azure Virtual Network

Before we move further, I think it is helpful to explain how to get a cloud server onto your VNET. After all, we can’t do it through the management portal; we have to do it at deployment.

Step 1: Get the Fully-Qualified Domain Name (FQDN)

To communicate between VMs and services in a VNET, you can use IP addresses or Azure-provided name resolution. Azure-provided name resolution in a VNET requires the FQDN, which you can get in the command prompt.

portal_19_find_fqdn

The "hostname" command gives you the expected VM name, in this case "vm-md-demo2a".

The "nslookup [hostname]" command provides the FQDN, which is the internal DNS name (http://vm-md-demo2a.vm-md-demo2a.j3.internal.cloudapp.net/) for this VM’s IP address, in this case "10.0.0.4".

Step 2: Modify the Azure project’s service configuration

You will need to add the Network Configuration to the Service Configuration (.cscfg) document:

<NetworkConfiguration>
  <VirtualNetworkSite name="vnet-md-demo2" />
  <AddressAssignments>
    <InstanceAddress roleName="MyCompany.Website">
      <Subnets>
        <Subnet name="Subnet-2" />
      </Subnets>
    </InstanceAddress>
  </AddressAssignments>
</NetworkConfiguration>

Once the cloud service is on the VNET, you can reference other members of the network by FQDN or IP address.

Limiting Access to a Virtual Machine

We successfully hit our first VM through the browser from our home PC, but now we should try from our second VM. Try the following 3 test cases, which should all work:

  1. Using external DNS name (i.e., http://vm-md-demo2a.cloudapp.net)
  2. Using FQDN (i.e., http://vm-md-demo2a.vm-md-demo2a.j3.internal.cloudapp.net/)
  3. Using VNET IP address (i.e., http://10.0.0.4)

Now, we are going to prevent access to our first VM from our PC, while retaining access for anything on the VNET. Before we begin, review the address space and subnets, because you will need the CIDR information that provides the IP address ranges.

portal_20_vnet_ip_addresses

For this article, we will specify the permitted IP addresses for the HTTP endpoint we previously created; not just for an individual subnet, which we could easily do, but for the entire address space. To start, navigate to the HTTP endpoint details for the first VM in the Azure portal, and modify the Access control list, at the bottom.

portal_21_manage_acl

Here, I provided a meaningful name, set the Action to "Permit", and the Remote Subnet to the CIDR of my VNET’s address space ("10.0.0.0/25"). Optionally, I could specify individual subnets to Permit or Deny.

NOTE: The ACL rules are evaluated in order, so you could allow a larger subnet, then restrict smaller pools of IP addresses. For more information, go to http://msdn.microsoft.com/library/azure/dn376541.aspx

Now, after the ACL configuration is applied, try accessing the VM from your home PC. It should fail.

portal_15_attempt_no_config

Try now from the second VM.

  1. Using external DNS name (i.e., http://vm-md-demo2a.cloudapp.net) should FAIL
  2. Using FQDN (i.e., http://vm-md-demo2a.vm-md-demo2a.j3.internal.cloudapp.net) should SUCCEED
  3. Using VNET IP address (i.e., http://10.0.0.4) should SUCCEED

So, we have successfully blocked outside network traffic from accessing our VM.

Why prevent access to your VM?

Great question. If you are building an API for consumption by your cloud services, or you have an Elasticsearch implementation with sensitive data, you surely don’t want to expose that functionality or data to the outside world. The endpoint ACLs are an excellent way to prevent that access and secure your services.

Conclusion

I had to pull a lot of this information together, but now you can see how to:

  • Create an Azure Virtual Network
  • Create and configure an Azure Virtual Machine on a Virtual Network
  • Deploy a Cloud Service inside an Azure Virtual Network
  • Manage Endpoints and ACLs to control access to a Virtual Machine

Hope this helps!

Mark Doyle is Software Architect at Collabroscape [collabroscape.com], and Founder & President of Scruddle [scruddle.com]. You can follow him on Twitter [@mark_doyle_ftw], or connect with him on LinkedIn [www.linkedin.com/in/spencermarkdoyle/].

Thursday, January 8, 2015

Configuring Azure Virtual Networks

Azure Virtual Networks provide a great way of allowing cloud services, websites, and virtual machines to communicate with each other, much like in an on-premise intranet. As a novice on the networking side, I had to learn some basics about these networks, and I'd like to pass on some of these lessons.

Summary

In this article, I will cover how to set up an Azure virtual network ("VNET"), how to set up an Azure virtual machine ("VM") on that VNET, and then how to configure that VM for web traffic, including endpoint and ACL configuration. As an added bonus, you'll see how to configure an Azure Web Role cloud service on the VNET, as well as how to restrict web traffic to only allow other VMs and services on the VNET.

NOTE – This article uses images from the previous version of the Azure management portal (http://manage.windowsazure.com). I’ll follow up with a version for the new portal (http://portal.azure.com).

Create the Azure Virtual Network

Obviously, since we are adding VMs to a VNET, we need to create both. To start, click New –> Network Services –> Virtual Network –> Custom Create, which allows us to specify detailed information on the address spaces and ranges.

Step 1: Specify VNET name and location

manage_01_create_vnet_step_1

Specify a meaningful name, then select the appropriate location. Here, I’ve specified “vnet-md-demo1”, and the South Central US location, since I’m closest to it.

Remember, you’ll get less latency if your storage accounts, services, VMs, and VNETs are in the same location (data center).

Step 2: Leave the DNS server information as-is

manage_02_create_vnet_step_2

Although it is not covered in the scope of this article, you can configure a custom DNS server, which can provide additional benefits. For this article, we will use Azure-provided name resolution. For more information on the features and considerations of each, go to http://msdn.microsoft.com/library/azure/jj156088.aspx

Step 3: Configure the address spaces, or IP ranges

manage_03_create_vnet_step_3

The address space configuration allows you to set up the IP ranges for the VNET, with multiple parent address spaces, each with multiple subnets. You need to specify the CIDR, but Azure provides the address count that each represents. You can do the same for the subnets, up to the number of addresses allocated in the subnet’s address space. If you are interested in learning more about CIDR: http://ip2cidr.com/cidr.php

For this article, I’ve created a single address space with 128 addresses, and the default starting IP. I’ve also created 2 subnets, each with 16 possible addresses and the default names (“Subnet-1”, “Subnet-2”), as next I’ll be creating a VM on each. You can build your network as you see fit, and even across subnets, the machines will be able to communicate with each other. This also applies to cloud services, both Web Roles and Worker Roles.

Create the Azure Virtual Machines

As mentioned above, we are going to create two VMs on our newly-created VNET, so we can demonstrate how they communicate with each other. To start, click New –> Compute –> Virtual Machine –> From Gallery, which allows us to specify the VNET before the VM is provisioned.

Step 1: Select the image

manage_04_create_vm_step_1

For this article, we’ll select Windows Server 2012 R2 Datacenter, which supports a wide variety of applications and web functionality. Plus it’s the data center edition, and that just sounds fun.

Step 2: Configure the VM

manage_05_create_vm_step_2

Provide a meaningful name, but make sure it is DNS-compatible – use letters, numbers, and hyphens. For this article, I’ve selected the latest version of the OS, the VM name “vm-md-demo1a”, I’ve selected A1, since I can run it all month without going over my credit limit. Provide the user name and password, and continue.

NOTE: Another great thing about VMs is that you only pay for what you use, meaning that if you shut the VM down, you don’t pay for it, even though the image still retains whatever you have installed. If you want to try out the beefy machines, like the A7, which has 8 cores and 56 GB RAM, you can run it for a few hours, then shut it down.

Step 3: Configure the VM for the VNET

manage_06_create_vm_step_3

Here, I left the Cloud Service DNS Name the same as the VM name, since it was DNS-compatible. Also, rather than selecting a Region, we will select the VNET, which then allows us to select which Subnet the VM will live in (I’ve selected “Subnet-1”). For now, leave the Endpoints alone, as we will come back to them in more detail.

Step 4: Finish the wizard

You can select other features, but finish creating the VM, and Azure will begin provisioning the machine.

Step 5: Configure the second VM

Follow the same steps to create another VM, except on Subnet-2 (or whatever name you specified for the second subnet), and wait until both VMs have been provisioned.

Configure VM as Web Server

So that I can test connectivity, I’m setting up the first VM as a web server, as this will likely be a common use for future Azure VMs you configure.

Step 1: Configure Windows

Connect to the VM with RDP, which you can do through the Azure management portal by clicking “Connect”.

Setting up the web server entails adding the additional Windows features for IIS, and the necessary extensions for ASP.NET, etc. These specific steps are outside the scope of this article, but there is an abundance of information on how to do so.

Step 2: Create a test page

Let’s create a test web page we can hit from our other VM and from our local PCs. By default, you’ll create the default web page in C:\Inetpub\wwwroot\. You can test with http://localhost/. Here, you can see I’m rocking the classic ASP:

manage_07_create_test_page

Step 3: Try to browse to the web VM from your home PC

Open up a browser, and try to hit [your-vm-name].cloudapp.net. It probably looked like this:

manage_08_attempt_no_config

Why didn’t it work? We were able to connect via RDP…

Because we need to open up the appropriate endpoint!

Add an Endpoint to an Azure Virtual Machine

Endpoints are like the firewall. We need to configure which ports are open, and they even support port redirects, just like a router. To start, go to your VM in the Azure management portal, and click the Endpoints option near the top, under the VM name. Once there, click Add.

Step 1: Select type of endpoint

manage_09_add_endpoint_step_1

For this article, we are configuring a stand-alone endpoint, but the Azure endpoints support load-balanced configurations.

Step 2: Configure the endpoint ports

manage_10_add_endpoint_step_2

Azure predefines quite a few commonly-used ports, including HTTP and HTTPS, but you can also create custom port endpoints. For example, a VM running Elasticsearch will need port 9200 opened.

NOTE: For non-typical ports like Elasticsearch, you may also have to configure the Windows Firewall to open up that specific port.

Step 3: Try again from your PC

After the endpoint has been created, try browsing to the VM again, and you should succeed.

manage_11_success_after_endpoint

Adding a Cloud Service to an Azure Virtual Network

Before we move further, I think it is helpful to explain how to get a cloud server onto your VNET. After all, we can’t do it through the management portal; we have to do it at deployment.

Step 1: Get the Fully-Qualified Domain Name (FQDN)

To communicate between VMs and services in a VNET, you can use IP addresses or Azure-provided name resolution. Azure-provided name resolution in a VNET requires the FQDN, which you can get in the command prompt.

manage_12_find_fqn

The “hostname” command gives you the expected VM name, in this case “vm-md-demo1a”.

The “nslookup [hostname]” command provides the FQDN, which is the internal DNS name (“…internal.cloudapp.net”) for this VM’s IP address, in this case “10.0.0.4”.

Step 2: Modify the Azure project’s service configuration

You will need to add the Network Configuration to the Service Configuration (.cscfg) document:

<NetworkConfiguration>
  <VirtualNetworkSite name="vnet-md-demo1" />
  <AddressAssignments>
    <InstanceAddress roleName="MyCompany.Website">
      <Subnets>
        <Subnet name="Subnet-2" />
      </Subnets>
    </InstanceAddress>
  </AddressAssignments>
</NetworkConfiguration>

Once the cloud service is on the VNET, you can reference other members of the network by FQDN or IP address.

Limiting Access to a Virtual Machine

We successfully hit our first VM through the browser from our home PC, but now we should try from our second VM. Try the following 3 test cases, which should all work:

  1. Using external DNS name (i.e., http://vm-md-demo1a.cloudapp.net)
  2. Using FQDN (i.e., http://vm-md-demo1a.vm-md-demo1a.j1.internal.cloudapp.net)
  3. Using VNET IP address (i.e., http://10.0.0.4)

Now, we are going to prevent access to our first VM from our PC, while retaining access for anything on the VNET. Before we begin, review the address space and subnets, because you will need the CIDR information that provides the IP address ranges.

manage_13_vnet_ip_addresses

For this article, we will specify the permitted IP addresses for the HTTP endpoint we previously created; not just for an individual subnet, which we could easily do, but for the entire address space. To start, navigate to the HTTP endpoint for the first VM in the Azure management portal, and click Manage ACL, at the bottom.

manage_14_manage_acl_1

Here, I provided a meaningful name, set the Action to “Permit”, and the Remote Subnet to the CIDR of my VNET’s address space (“10.0.0.0/25”). Optionally, I could specify individual subnets to Permit or Deny.

NOTE: The ACL rules are evaluated in order, so you could allow a larger subnet, then restrict smaller pools of IP addresses. For more information, go to http://msdn.microsoft.com/library/azure/dn376541.aspx

Now, after the ACL configuration is applied, try accessing the VM from your home PC. It should fail.

manage_08_attempt_no_config

Try now from the second VM.

  1. Using external DNS name (i.e., http://vm-md-demo1a.cloudapp.net) should FAIL
  2. Using FQDN (i.e., http://vm-md-demo1a.vm-md-demo1a.j1.internal.cloudapp.net) should SUCCEED
  3. Using VNET IP address (i.e., http://10.0.0.4) should SUCCEED

So, we have successfully blocked outside network traffic from accessing our VM.

Why prevent access to your VM?

Great question. If you are building an API for consumption by your cloud services, or you have an Elasticsearch implementation with sensitive data, you surely don’t want to expose that functionality or data to the outside world. The endpoint ACLs are an excellent way to prevent that access and secure your services.

Conclusion

I had to pull a lot of this information together, but now you can see how to:

  • Create an Azure Virtual Network
  • Create and configure an Azure Virtual Machine on a Virtual Network
  • Deploy a Cloud Service inside an Azure Virtual Network
  • Manage Endpoints and ACLs to control access to a Virtual Machine

Hope this helps!

Mark Doyle is Software Architect at Collabroscape [collabroscape.com], and Founder & President of Scruddle [scruddle.com]. You can follow him on Twitter [@mark_doyle_ftw], or connect with him on LinkedIn [www.linkedin.com/in/spencermarkdoyle/].

Sunday, November 2, 2014

Calculating a SHA256 hash in .NET [SNIPPET]

As the programming world moves toward REST API for software interactions, we find the need for securing our requests becoming more apparent.
Some 3rd party APIs you consume specify a computed hash to verify the caller’s authenticity, such as Facebook’s “app secret proof” querystring parameter (https://developers.facebook.com/docs/graph-api/securing-requests/), which utilizes a SHA256 hash of the access token and client secret. They demonstrate the ability to compute this hash with PHP:

$appsecret_proof= hash_hmac('sha256', $access_token, $app_secret);

So, I figured it would be useful to demonstrate one way to do this with C# and .NET:

public static string ComputeHmacSha256Hash(string valueToHash, string key)
{
    byte[] keyBytes = Encoding.ASCII.GetBytes(key);
    byte[] valueBytes = Encoding.ASCII.GetBytes(valueToHash);
    byte[] tokenBytes = new HMACSHA256(keyBytes).ComputeHash(valueBytes);
    valueBytes = null;
    keyBytes = null;

    StringBuilder token = new StringBuilder();
    foreach (byte b in tokenBytes)
    {
        token.AppendFormat("{0:x2}", b);
    }
    tokenBytes = null;

    return token.ToString();
}


In the Facebook app secret proof scenario, the “valueToHash” is the access token, and the “key” is the OAuth API key’s client secret.
HMACSHA256 is in the System.Security.Cryptography namespace, and obviously Encoding.ASCII is in System.Text.

Tuesday, March 18, 2014

A Pain in the MAS

I recently completed a small project for a client that involves printing barcodes (easy), but required exporting sales order data from their Sage accounting software, MAS 90 (not so fun). Specifically, I had to write a .NET application to export the data, which queried the MAS 90 database with ADO.NET and ODBC. So, I am publishing some of those lessons in case it helps someone else.

Configuring the DSN

You can query the MAS 90 database with ODBC, but you will need to configure a System DSN. Installing the Sage MAS 200 Desktop will place the “MAS 90 4.0 ODBC Driver” files on your system.

If you are running a 32-bit version of Windows, launch “Data Sources (ODBC)” from the Administrative Tools menu. If you are running a 64-bit version (like I am), you will need to use the 64-bit version of the ODBC manager, which is located at:

%windir%\SysWOW64\odbcad32.exe

Once the ODBC manager is running, click the System DSN, then the Add button, select “MAS 90 4.0 ODBC Driver” , and click finish. Note: If you do not run the appropriate version of the ODBC manager (32- versus 64-bit), you will not see the installed MAS driver.

Writing the Queries

I love SQL Server and T-SQL, and I’ve been using it for years. The syntax, the useful functions… You don’t get all that stuff in this scenario. Sorry. You have to stick with ISO standard SQL.

I found the best way to build and test my SQL statements was with Visual Studio Server Explorer, since you can use the query builder, and the System DSN to connect and issue those queries. Just make sure they work before trying them in your application!

Connecting with ADO.NET

Once the DSN is created, you will use it in your connection string in your App.config or Web.config:

  <connectionStrings>
    <add name="MAS90Database"
         connectionString="DSN=MyMAS90DSN"
         providerName="System.Data.Odbc" />
  </connectionStrings>

I should point a few things here:

1.) If you have specified the user name, password, and company in the DSN settings, you DO NOT have to specify them in the connection string; if they are not specified in the DSN settings, then you MUST specify them in the connection string:

connectionString="DSN=MyMAS90DSN;UID=<username>;PWD=<password>;Company=<company>;"

2.) The provider is “System.Data.Odbc”, which instructs ADO.NET to use the ODBC libraries instead of the default SQL Server libraries.

Putting it All Together

In your .NET application, your database connection options are pretty flexible. You can use ADO.NET or the Enterprise Library Data Access block (but I haven’t tried Entity Framework). You can also execute the queries and return DataSet objects and IDataReader objects!

Note: If you are compiling on a 64-bit machine, you will probably have to compile to target “x86”, instead of “AnyCPU”. This has to do with the MAS drivers being 32-bit. I first tried executing code that was compiled with “AnyCPU” on the destination machine, and received an ODBC exception. Once I compiled with “x86”, everything worked.

Hopefully this helps someone out there!

Mark Doyle is Software Architect at Collabroscape [collabroscape.com], and Founder & President of Scruddle [scruddle.com]. You can follow him on Twitter [@mark_doyle_ftw], or connect with him on LinkedIn [www.linkedin.com/in/spencermarkdoyle/].

Wednesday, June 26, 2013

Windows Azure Autoscaling (Preview)

I was working on a production deployment for a client, and found something that (almost) made me forget what I was doing. If you have logged into the Windows Azure portal lately (like, today), you might notice the green PREVIEW badge next to the Scale menu option:

Scale_1

Yes, autoscaling is now an option configurable from the Azure management portal. You no longer need external tools like the Enterprise Library autoscaling application block, which I had been meaning to look into…

Obviously, you can leave autoscaling off, or configure it by CPU or queue, and configure options for each.

Scale by CPU

Scale_2

To scale by CPU, you primarily focus on the target CPU load (indicated in this image by 60-80%). At that range, the autoscaling process would scale the instance up by the specified number until load % is reduced, and continue to do so if needed after the specified wait time.

A real-world scenario where this is useful is a web role handling peak traffic.

Scale by Queue

Scale_3

To scale by queue, the autoscaling process monitors the number of messages in an indicated queue, and can scale up and down based on that number.

The real-world scenario here is a worker role that is responsible for handling messages in a queue. Think ecommerce order fulfillment or a content indexer.

Bottom Line

The bottom line? This is awesome, and Microsoft continues to give developers and companies reasons to build on Windows Azure.