This is the second in a series of tutorials presenting the steps necessary to install and configure a new SQL Server database to work with Microvellum. 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.) Prior to 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 Fig. 1. 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.”
2.) Click “Yes” in the User Account Control box if asked whether you want to allow the app to make changes to your device.
3.) 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.
4.) 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.
5.) Close the Configuration Manager by clicking the small x in the upper right corner.
6.) Click here to download the latest General Availability version of SQL Server Management Studio (SSMS).
7.) Right click on the downloaded install file and select "Properties". Check the checkbox for "Unblock" at the bottom if is available. Click Apply and OK.
8.) Again, right click on the downloaded file and select “Run as administrator.”
9.) Click “Yes” on the User Account Control box that asks if you want to allow the app to make changes to your device.
10.) Click Install to accept the software License Terms. Click Close when the installation is complete.
11.) Now you will configure 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.”
12.) 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.
13.) 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.
a. - 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.
14.) When the Management Studio is open, right click Databases, and click New Database.
15.) Give the new database a name in the New Database screen. In our 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.
16.) 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..."
NOTE: 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 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 resolution.
17.) There are two different types of logins with a different application for each. The two types are Windows authentication and SQL Server authentication.
a. - If you are on a domain, we recommend that you create a Windows authentication type login.
b. - If you are on a Peer-to-Peer network, we recommend that you create a SQL Server authentication type login.
c. - The best overall option is to create user logins in Windows mode.
NOTE: 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.
d. - To create a Windows type user login, click the “Search…” button.
e. - In the Select User or Group window, click the “Locations…” button and select "Entire Directory" or your domain server name, and then click OK.
f. - 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.
18.) 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.
a. - 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.
19.) The last step in configuring your new database is to map the Users to the databases that they will have permissions to use.
a. - Open a user login you have created and click on "User Mapping" in the left column. In 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.
b. - Check the checkbox “db_owner” in the area named “Database role membership for:” and when finished, click OK.
In order 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 an article discussing general data import.
Click here to view an article discussing specific Unified Work Order Database data import.