Granting access to Microsoft SQL Server for Salescast (hosted on Windows Azure)

Granting access to Microsoft SQL Server for Salescast


Home » Salescast » Intermediate SQL schema » Here

Salescast is hosted on Windows Azure, the public cloud of Microsoft. This computing environment comes with certain restrictions, most of them to strengthen the overall level of security of the platform. This page details the gotchas to make your Microsoft SQL Server instance reachable from Salescast - so that forecast reports could be produced. Microsoft SQL Server 2005 and later versions are supported.

  1. The Windows Server has to be accessible from our Windows Azure Datacenter segment where Salescast resides. Usually this access is configured by a System Administrator (or Network Administrator) of a company and involves port forwarding of MS SQL Server (mapping it to a public IP address) and allowing inbound TCP connections from "94.245.*.*"
  2. Salescast does not have Virtual Private Networking (VPN) capabilities, so VPN connections will not work. Salescast needs to know the exact IP address (with a port) which could be accessed to establish a connection to the MS SQL Server.
    The MS SQL Server itself has to both allow incoming connections and have "TCP/IP Protocol Enabled". This is configured by Database Administrator of company in MS SQL server management console. Also see (KB823938)
  3. "Named SQL Server instances" (i.e.: 127.0.0.1/SQLExpress) use dynamic ports to accept incoming database requests (a specific service is offered by MS SQL Server to resolve the final port through UDP datagrams). Named SQL instances are not accessible from the cloud. Please, configure your server to listen on a specific TCP port. See (KB823938 and 177440)
  4. Salescast needs to be able to login to the SQL Server. In order to do so, Salescast need valid user/password that uses SQL server authentication (Windows Authentication will not work). This access is typically delivered by the Database Administrator. It is advised to make the password as strong as possible (i.e.: mix of numbers, letters and symbols that is at least 12 characters long). Salescast will always attempt to use encrypted SQL connections.
  5. The SQL login needs read access to the targeted database (or at least to the relevant subset of tables). Your Database administrator should be able to set up this configuration. Although, we dedicate a lot of effort in making the integration process as secure as possible, we suggest not to grant any write access to Salescast, unless you really know why you are providing this right (Salescast typically only need extremely narrow write accesses).

You can verify external connectivity to the database by trying to login to it remotely (outside of company intranet) with MS SQL Server Management Studio, using the provided IP address, TCP port (they are put into "Server Name" in format of "12.12.12.12,3455"), username, strong password and database name. You should have only the read access to the integration database. Note, that you might need to temporarily allow your current IP address through the firewall as well.

Windows Azure Partner of the Year logo


What people say

Classical solutions require too much manpower and don't scale correctly over hundreds of thousands of products. Lokad and Windows Azure were exactly the solution my business needed. Pierre-Noël Luiggi, CEO of Oscaro
The Lokad forecasting solution allows us to precisely forecast our sales and to optimize our inventory accordingly. The result is there: we are maintaining a 99% customer satisfaction level and deliver food that is often fresher than what can be found at local pet stores. Anthony Holloway, CEO at k9cuisine

More success stories.



In the press

Image