daknetworks.com

MS SQL setup is a PITA. Here are a few of my notes:

1-the install package is the only way to install databases. In other words, if you have one database and you want another, you have to go through the setup process again. So keep that SQL INSTALLATION SETUP file on the system.

2-the versions are wacky. There is:

• SQL - costs for license.
• SQL EXPRESS - free for up to 10GB.
• SQL CE (or compact edition) - Meant to be used in use with an application.
• SQLITE - I don't know what this is for.

3-to connect and manage the SQL, you have to install SQL MANAGEMENT STUDIO. Think of this as their version of PHPMYADMIN. It can actually control different versions at the same time. It can control a 2012 SQL database and a 2014 SQL database at the same time.

4-which leads me to my next point. SQL versions can coincide. Both 2012 and 2014 can run at the same time.

5-permissions are wacky. They just are. They can be either SQL permissions or they can be WINDOWS permissions. But even if you use SQL permissions, you might have to setup WINDOWS permissions anyway. This is for a local LAN installation.

6-when you install, it automatically adds your USERNAME as the owner of the database. This is required so that you can add/remove other user permissions.

7-to see/add/change/remove the permissions:
(good video to explain the below: https://www.youtube.com/watch?v=gsr8ID2pY-A&feature=youtu.be)

• expand the DATABASE-INSTANCE name.
• expand the SECURITY folder
• expand the LOGINS folder
• right-click LOGINS
• click NEW-LOGIN

Here, you can see where the permission can be either WINDOWS or SQL.

I find it's easier to use the WINDOWS AUTHENTICATION (although it doesn't seem like it should be so). The reason is that when the APP SERVICE runs (whatever APP is being used), the SERVICE is being run as the current-logged-in-user. I find (and this may be incorrect) that if you use the SQL SERVER AUTHENTICATION (like I want to), then you also have to go back and add the current-logged-in-user as well. This can add up to quite the number if you have many users.

To get around this, I add a specific DATABASE-USER account in ACTIVE-DIRECTORY. Then I change all the APP SERVICE on the clients machines to run as the DATABASE-USER (rather than the current-logged-in-user). This is done in SERVICES.MSC. Then I add that DATABASE-USER to the permissions on the SQL MANAGEMENT STUDIO.

• select the DATABASE-USER.
• leave the rest as the defaults.

Now you have to add this user to the DATABASE.

• select USER MAPPING (on the left-hand side).
• select the DATABASE you are controlling.
• click OK (at the bottom).

After this is done (and only after), you now have to add permissions to the DATABASE for this user.

• expand the DATABASES folder.
• right-click the DATABASE name.
• select PROPERTIES (at the bottom).
• click PERMISSIONS (on the left).
• select the USER (in the list).
• place a CHECKMARK in the GRANT column for the following
• DELETE, EXECUTE, INSERT, SELECT, UPDATE

8 -for the client machine to see and connect to the SQL DATABASE, you have to allow the port through the firewall.

9 -the port for each instance is randomly assigned.

10 - to find the port number, you have to use the SQL SERVER CONFIGURATION MANAGER.

• open the SQL SERVER CONFIGURATION MANAGER.
• click SQL SERVER NETWORK CONFIGURATION (on the left-hand side).
• click on the DATABASE you are working on.
• double-click TCP/IP (on the right-hand side).
• click IP ADDRESSES (at the top)
• scroll to the bottom.
• find TCP DYNAMIC PORTS
• mine says 51772

11 -you have to allow 2 PORTS through the WINDOWS FIREWALL.

• random assigned TCP port.
• UDP PORT 1434 (notice that this is a UDP PORT, not a TCP port).

I will post more as I come across.