QuoteWerks Web - Setting up QuoteWerks Web to have access to your SQL server

Steps to integrate QuoteWerks Web with your on-premises QuoteWerks Database.

** Please Note: The "quick-start" steps defined here do not take security best practices into consideration. It is strongly recommended that you also review and adhere to the "Security Suggestions" section below.


Quick-Start Overview:
-----------------------------

From a high-level there are only 3 steps required to integrate QuoteWerks Web with your on-premises QuoteWerks Database.
1) Ensure your Database Server is setup for Mixed Mode authentication.
2) Configure your hardware firewall to route incoming SQL traffic to your Windows Server that is running SQL Server.
3) Configure the firewall on your Windows Server (that the SQL Server is on) to not block the incoming traffic that you just routed from the hardware firewall to your SQL Server.
4) Complete the QuoteWerks Web setup process in QuoteWerks

Each of these high-level steps will now be discussed in further detail.


Quick-Start Steps In-Depth:
-----------------------------

/// Ensuring your Database Server is setup for Mixed Mode authentication------------------------------------------------------------------------------------
Please note SQL Server administration is outside the scope of this document. The steps provided below are simply to point you in the right direction. Please consult your SQL Server administrator for further assistance.

1) Launch the Microsoft SQL Server Management Studio
2) Right-click the SQL server root node in the Object Explorer
3) Click Properties
4) Click Security in the left pane
5) In the right pane under "Server Authentication" select "SQL Server and Windows Authentication Mode"
* Note: Please see the "Security Suggestions" section below for additional suggestions

/// Configuring your firewall to route incoming SQL traffic to your SQL Server-------------------------------------------------------------------------------------
Please note firewall administration is outside the scope of this document. The steps provided below are simply to point you in the right direction. Please consult your firewall administrator for further assistance.
While it's important to note that many firewalls have differing capabilities at a minimum they all generally have support for basic port forwarding. Not taking additional security concerns into consideration there is really only a single step here:

1) On your hardware firewall/router, add a port forwarding rule / entry in your firewall to forward external SQL port (default is TCP/IP 1433) requests to your internal SQL Server.
* Note: Please see the "Security Suggestions" section below for additional suggestions

2) Configure the firewall on your Windows Server (that the SQL Server is on) to not block the incoming traffic on port 1433 that you just routed from the hardware firewall to your SQL Server.

3) If you are using SQL Server Express, you will need to make the changes discussed here.


/// Completing the QuoteWerks Web setup process in QuoteWerks -----------------------------------------------------------------------------------
At the time of this writing the QuoteWerks Web setup process can completed as follows:
1) Navigate the top menu: Tools->Options
2) Click "Cloud Account" tab
3) If not already entered input your Tenant Account Number and Password
4) Verify everything is correct by clicking the "Test" button
5) Click "QuoteWerks Web setup"
6) Verify your QuoteWerks Web Account Status by clicking "Check Account Status"
7) Enter your external IP address in the "SQL Server Host". Note if you've also changed the SQL port this specified by placing a comma and then the port number after the IP address. Ex: 24.32.132.43,6300
8) Enter your SQL Server Username and Password
9) Save Settings
10) To verify QuoteWerks Web can communicate with your SQL Server click "Test Settings"


At this point QuoteWerks Web should be fully integrated with your on-premises QuoteWerks database.


Security Suggestions:
-----------------------------

-Configure your firewall to limit incoming SQL Server port access to Only QuoteWerks Web's IP Address (209.208.98.51).
-Change your SQL Server port (or even just the external port portion of the port-forwarding rule if your firewall supports this).
-Create and use a SQL account whose access is limited to just the QuoteWerks database.
-Be sure all SQL user accounts have very STRONG passwords
-If possible disable the built-in SQL sa user account

It is also recommended that you consider the security suggestions outlined in the following articles (and any other Microsoft approved sources):
http://sqlmag.com/blog/staying-safe-coming-storm
http://sqlmag.com/blog/sql-server-authentication-internet-facing-servers
http://sqlmag.com/blog/ipmuncher-internet-facing-sql-servers


Additional points:
-----------------------------
A simple way to determine your external IP address is by Googling: "external ip address". Google provides your Public IP address at the very top of the search results.
Also sites like https://www.whatismyip.com/ will provide you your public IP address.