Configuring SQL Express Database (Tutorial 2)

Configuring an SQL Express Database (Tutorial 2)

This is the second in a series of tutorials presenting the steps necessary to install and configure SQL Server to work with Microvellum. This tutorial focuses on the concluding steps when configuring the new database instance you created previously to work with Microvellum, and adding the two necessary databases. Click here to view the first tutorial in the series.

After installing the SQL Server 2017 Express software and creating a named instance, as explained in the previous tutorial, this tutorial will present the steps necessary to create a new database and add new users.
  1. Before using SQL Server Express with Microvellum, you must configure it to work in a network environment. To do this, start the “SQL Server 2017 Configuration Manager.” If using Windows 10, press the Windows key and begin entering “SQL Server 2017 Configuration Manager” in the search bar, as shown in the image below. When the desktop app is displayed, either click it or press the Enter key. Alternatively, press the Windows key and scroll down to the M section. Find and click “Microsoft SQL Server 2017 > SQL Server 2017 Configuration Manager.”

  1. Click “Yes” in the User Account Control box if asked whether you want to allow the app to make changes to your device.
  2. In the SQL Server Configuration Manager dialog box, expand “SQL Server Network Configuration” in the left half of the window and click on “Protocols for <named instance you created in the first tutorial> - in this case, Protocols for SQL2017EXPRESSDM. Double click on TCP/IP on the right side of the screen, and set the Enabled property to Yes. Click Apply and OK.

  1. You now need to activate the modification you made by restarting the SQL Server Service. In the same SQL Server Configuration Manager dialog box, expand “SQL Server Services” on the left, and right-click on “SQL Server(<your_instance_name>).” Click “Restart” and wait for the service to be restarted.

  1. Close the Configuration Manager by clicking the small x in the upper right corner.
  2. Click here to download the latest General Availability version of SQL Server Management Studio (SSMS).
  3. Right-click on the downloaded install file and select "Properties." Check the checkbox for "Unblock" at the bottom if it is available. Click Apply and OK.
  4. Again, right-click on the downloaded file and select “Run as administrator.”
  5. Click “Yes” on the User Account Control box that asks if you want to allow the app to make changes to your device.
  6. Click Install to accept the software License Terms. Click Close when the installation is complete.
  7. Now you will configure the properties of the database. If using Windows 10, press the Windows key and begin entering “Microsoft SQL Server Management Studio 17” in the search bar. When the desktop app is displayed, either click it or press the Enter key. Alternatively, press the Windows key and scroll down to the M section. Find and click “Microsoft SQL Server Tools 17 > Microsoft SQL Server Management Studio 17.”
  8. The first box you should see when the Management Studio opens contains the default server name. If the second half of that name does not match the server instance, you set up in tutorial #1 of this series, drop down the list, click Browse, and select the correct instance. The server name is a combination of the Computer Name and Database Instance Name as shown in the figure below. You will use this server name when you set up data access in Microvellum. Make a note of the password in a secure location.
  9. Drop down the list for Authentication and select “SQL Server Authentication” if you followed the procedure in tutorial #1. Depending on how the instance was set up, use the SA account to login, or your Windows login to connect.
    1. If connecting via SQL Server Authentication, enter “sa” (without the quotation marks) for the Login and Password that you set up when creating the server instance in tutorial #1.
  10. When the Management Studio is open, right-click Databases, and click New Database.
  11. Give the new database a name in the New Database screen. In this example, I have named it to correspond with the library version that will be used in conjunction with the database in a future step. Click the OK button when finished.

  1. You now have a new empty database, and a login will need to be created for each user who will be connecting to this database. Set up individual user accounts by double-clicking on Security and right-click on the Logins folder. Select "New Login..."
  2. You must use individual accounts instead of Windows Group profiles if using SQL Server 2008 R2 and earlier. If you use Windows Group profiles for databases of these versions, multiple users in the group will get an error and not be able to access or modify certain data. Using SQL Server 2012 and later, it is now possible to define a default schema for a Windows Group. Click  here for a general article explaining this issue and its resolution.
  3. There are two different types of logins with a different application for each. The two types are Windows authentication and SQL Server authentication.
    1. If you are on a domain, we recommend that you create a Windows authentication type login.
    2. If you are on a Peer-to-Peer network, we recommend that you create a SQL Server authentication type login.
    3. The best overall option is to create user logins in Windows mode.
    4. You will need to know which types of logins were created when you come to the data access options in Microvellum. Make a note of the type in a secure location.
    5. To create a Windows-type user login, click the “Search…” button.
    6. In the Select User or Group window, click the “Locations…” button and select "Entire Directory" or your domain server name, and then click OK.
    7. Enter a user name in the box under “Enter the object name to select” and click the button Check Names. This searches for that user on the network to have access to the SQL server data. Type in the available field the username or the person's name you are trying to add and select the "Check Names" button. Once it has underlined itself as in the figure below, click OK and OK a second time to create the login.       

  1. If you need to create a user login of the type SQL authentication, click the button for “SQL Server authentication” on the Login – New window and enter a Login Name and Password.
    1. The Management Studio may inform you that the password is not complex enough, If so, either uncheck the box Enforce password policy, or use a more complex password that complies with the password policy.
  2. The last step in configuring your new database is to map the Users to the databases that they will have permissions to use.
    1. Open a user login you have created and click on "User Mapping" in the left column. In an area named “Users mapped to this login,” select the database you are allowing the user to access and check the checkbox next to that database. Typically it will be the database you just configured in this tutorial.
    2. Check the checkbox “db_owner” in the area named “Database role membership for:” and when finished, click OK.

To begin using your new database, you will need to configure Microvellum to use the SQL Server database you just configured.

Click  here  to view the next article in this series that discusses connecting Microvellum to your newly created SQL database to create a Unified Work Order Database (UWOD).
    • Recent Articles

    • Toolbox Release Notes | Build 24.1.0703.641

      The following release notes apply to Toolbox build 24.1.0703.641 Product Comments Fix An issue with product comments in has been resolved. It was found that in older builds (pre-2023), when a formula driven product had comments placed into it and the ...
    • Foundation Hardware Highlight: Richelieu

      Microvellum’s Foundation Library is built on the premise of providing our users with the best quality hardware for their construction projects, be they commercial, residential, or anything otherwise. As such, Toolbox has hardware from multiple brands ...
    • Deployment of Microvellum to Azure

      Choosing the right sized virtual machines (VM) in Azure depends on several factors, including your workload requirements, budget, and expected performance. You will need to work closely with an IT professional experienced in Azure to deploy Virtual ...
    • Toolbox Release Notes | Build 24.1.0625.641

      The following release notes apply to Toolbox build 24.1.0625.641 Ramping Fix There was reportedly an issue occurring with ramping wherein ramping would not function properly when utilized on a 4-sided cutout machine token. When attempting to ramp ...
    • Microvellum Foundation Library Release Notes | Build 24.0624

      The following release notes apply to Microvellum Foundation Library, Build 24.0624 Additions Added additional sample Generic Five Piece solid wood door styles: Raised Panel 5pc with Applied Molding Mitered Solid Raised Panel 5pc Mitered Haunched ...