SQL Configuration

The following instructions explain a default setup. If you already have an instance of SQL Server, please check with an SQL administrator before making any changes.

The following explains how to:

  1. Set SQL Server Authentication Mode

  2. Create a new database

  3. Create a new user

  4. Set the TCP Port for inbound connections

 

  • Firstly, open SQL Server Management Studio (SSMS) and login to the server you require

    • If this was set up as default it should look something like this:

  • Make SQL Server authentication mode available

    • Right click on the Server name at the top left and select Properties

    • Select Security on the left-hand side, choose SQL Server and Windows Authentication mode and click OK

    • You may get asked to restart the server. Please check that this server is not in use by any other users before completing this action

  • Create a new user:

    • Expand Security and right click Logins, now select New Login… from the options

    • Add the login name PI_Sage50 (this can be anything you like but remember the details as you will require these later) and select SQL Server Authentication. Use the password dashboard (this is the default password used in the pi data connections but can be anything you like; you will just be required to change these details later) and untick Enforce password policy as shown below:

    • Select Server Roles on the left-hand side, tick sysadmin and click OK

  • Create a new database:

    • Right click on Databases and select New Database…

    • Name the new database PI_Sage50 (this can be anything you like but remember the details as you will require these later) and click OK

  • Set the TCP Port for inbound connections:

    • Open Computer Management from the start menu and find SQL Configuration Manager

    • Expand SQL Server Network Configuration

    • Select Protocols for SQLEXPRESS

    • Right click on TCP/IP and select Properties

    • On the Protocol Tab, make sure this is set to Enabled

    • On the IP Addresses tab, scroll right to the bottom and set the TCP Port as 1433

    • You will be asked to restart the SQL Server (this is JUST the SQL Server, not the machine server). Please check that this server is not in use by any other users before completing this action

    • The Server can be restarted by Selecting SQL Server Services on the left-hand side, right-click the SQL Server in the central console and click Restart

 Please take a look at the following sections for more information:

Microsoft SQL Server Express Installation

Setting up the Sage ODBC connections (adding a company)

Setting the connection details (Sage 50 & SQL Server)

Running the ETL process

Installing and Configuring pi

Troubleshooting your Sage 50 installation