Databases 1a: Connecting with SSMS

While simple tables are useful to store most tabular data, you also have another option. Use database tables to store large, synchronized, or secure sets of tabular data.  Here's how using SQL Server Management Studio (SSMS), a tool for Windows


Before You Begin...

  • Review the differences between Local Tables and Database Tables.  For most use cases, simple tables are easier to use and maintain. Begin prototyping with local tables, knowing you can always scale up to database tables later.
  • Confirm your workstation runs on Windows OS.  SSMS is not available on other platforms. If you use macOS or Linux, try Azure Data Studio instead, which offers many of the same features.
  • Be sure you have the ability to install software onto your workstation.

 

1. Gather CPQ Database Login Credentials

Connecting to your organization's database requires a separate username and password. The credentials you use to log in to your -dev, -text, or -prod domains won't work. If you haven't connected to your database before, create a ticket at the EpicCare support desk. Ask for "Epicor CPQ database credentials for the XXX-dev.kbmax.com environment" where XXX is your usual organization name. When you receive the following connection information, save it somewhere secure.

  1. Servername
  2. Database name
  3. User name
  4. PW

If your company doesn't already have an account with EpicCare, or you're not sure how to start, use this link to learn more: https://www.epicor.com/en-us/customers/epiccare-support/

2. Update your Workstation Firewall Rules

If you're working behind a corporate firewall, ensure that network port 1433 is allowed between your workstation and the internet address of the servername from the previous step. This is the port you'll use to communicate directly with the database.

3. Update CPQ Firewall Rules

For security purposes, no location on the Internet is allowed direct access to your database server by default.  To allow access, each IP address connecting to your database must be added to the firewall allow list. Examples to be whitelisted include your workstation running SSMS, or an ETL server trying to load the database, or some other system. 

  1. Gather your public IP address. 
    To find your public IP address, search the web for “what is my IP address” from the console of the system you would like to authorize. Web sites that tell you your public IP address are listed. You'll want to use the IPV4 address, if both IPV4 and IPV6 are listed.
  2. Place the address into the firewall rules.
    In the Epicor CPQ admin portal under Infrastructure > Database Firewall, Add the IP range to the allowlist. You can...
    • be specific, and place the exact IP address in both the begin and end of the range. For example, "185.15.59.226" and "185.15.59.226"
    • be general, and allow a range of addresses that includes the exact address. For example, "185.15.59.200" and "185.15.59.300"


Public IP addresses can change.
Setting up your own workstation?

Your IP address can change periodically, or even every time you connect. If you have connection problems, they are usually solved by adding a new firewall rule. Try to use a connection that has a greater chance of using a static IP, such as the connection at your company’s office or connecting to your company’s VPN.  If you travel often and need secure access from anywhere, we suggest you use a virtual desktop, and provide the IP address of that desktop.

Setting up a new service?

If you are connecting a new service, such as an ETL tool, work with your IT department or the ETL provider to learn the proper public IP address of that service.  You may be given not just one IP address, but a collection of them or even an IP range.

Don't forget your own firewall.
These instructions only describe setting up inbound database connections through our firewall. Your own firewall may also need to allow outbound access to CPQ. Learn more about suggested settings for your firewall.

4. Install SSMS

Download the Microsoft SQL Server Management Studio (SSMS) installer on your Windows computer. Once downloaded, run the installer and follow any onscreen prompts to complete the installation.

5. Configure SSMS for your Database

Once you have installed SSMS, allowed your IP address in the firewall, and have received the Epicor CPQ database login information, you're ready to connect.  Launch SSMS on your computer. In the Connect to Server window,

Troubleshooting SSMS Connection Issues

What's next?

  1. Enter the Server Name you were given.
  2. Change Authentication to “SQL Server Authentication"
  3. Enter your Login of "xuser"
  4. Enter the Password you were given.


  5. At the bottom of the window, click Options >> to expand the window.
  6. At the top of the Connect to Server Window, click the Connection Properties tab.
  7. In the Connect to Database box, paste the database name of the org you would like to connect to. The naming convention is typically the following (where companyname is your company name):
    • kbmax_prod_companyname_dev
    • kbmax_prod_companyname_test 
    • kbmax_prod_companyname_prod
      (Usually, you will do your work in the dev database first, and then move that information to the test and production databases.)
  8. Verify Network protocol is set to <Default> and the Encrypt connection box is checked.

  9. Click Connect
  10. What happens next depends on if you can connect successfully.
    • If successful, then MSSMS should display the database tree in a left-hand pane. Click the + symbols to expand the tree.

    • If the connection doesn't work, use the troubleshooting messages to resolve.

 

  • Your client IP address does not have access to the server
    Depending on your internet connection, your IP address may change, even from one day to the next. Simply note your current IP address shown in the dialog box, and add it to the allow list as described above.  If you move locations constantly, you will almost always get a new IP address.  It may be easier to always be in the same place when trying to connect to the Epicor CPQ database server, or install SSMS on a cloud-hosted virtual desktop that has a more static IP address.
  • A connection attempt failed because the connected party did not properly respond...
    Verify that your local IT department does not have port 1433 blocked in the firewall.  This port should be open for your endpoint to the following subdomain:
    • servername contains "US": IP addresses for the subdomain kbmax-us.database.windows.net
    • servername contains "EU: IP address for the subdomain kbmax-eu.database.windows.net
  • No such host is known
    Verify the servername was typed correctly and try again.
  • Other problems
    You can verify your connection by following these steps on the computer which has SSMS installed:
    1. Start the "command prompt" desktop app: a new black window appears.
    2. In that window, type "ping <servername>" and press return.  <servername> is the server name you were given earlier.
    3. The window will show 4 timeouts.  This is normal: for security purposes, a firewall prevents your server from pings.  Note the IP address shown in the ping statistics.

    4. If the ping is successful, then try "telnet <ip address> 1433" and press return. <ip address> is the IP address you saw in the previous step. You may need to install telnet first.
    5. If you connect successfully through telnet, all text will disappear within the command prompt window.  It will appear blank. If you see this, congratulations! Close the comamnd prompt window to end the telnet session and quit from the command prompt.
    6. If you cannot connect, then work with your ISP to unblock port 1433.  For example, you can provide these same six steps to them, for their own troubleshooting.

Now that you're connected, you may want to...

  • create your own database tables for use in your configurators,
  • create views to combine or filter information in your database tables for use in your configurators, or
  • synchronize your database tables with external sources, like ERP or PLM systems like Kinetic.

Related articles



Was this article helpful?