- In order to work within the server, and gain access to the various databases and tables of information that we're storing, we'll use a program called SQL Server Management Studio. Management Studio is often referred to by its acronym, SSMS, and it'll provide a graphical interface to the server's content, so that we don't have to rely on a text-based interface, like the one that we saw when we were using the SQL Command window during the installation process. I've added a shortcut to Management Studio down on my start menu, so I'll go ahead and start it up from there.
When it starts up, I'm presented with this connect to server log-in window. First, I'll confirm underneath server type that we're logging in to the database engine. The other options are to log in to an analysis services, reporting services, or integration services. I'll also verify the name of the server that I'm going to connect to. Right now on my machine, it's called Windows 10. Since I only have one instance, it's simply the name of my computer. If you have multiple instances installed, or are connecting to a remote instance, then the format that this takes is the name of the computer followed by a backslash, and then the name of the server instance.
Next we need to provide our log-in credentials. SQL Server manages the users that connect to it in order to safeguard the data within. Some users, called administrators, will have the ability to alter anything that they wish. Other users may be granted a small subset of permissions, such as the ability to add data to a table, but not create new tables, or the ability to read data only, but not alter any data. Because of this, SQL Server needs to know who we are in order to assign us the correct permission levels. It does this by supporting several different authentication methods.
When we installed SQL Server, it automatically established the current Windows user as the database administrator, using a method called Windows Authentication. The other methods, that we can see by using the drop-down menu, are SQL Server Authentication and then Active Directory. With SQL Server Authentication, we can establish users and roles within the server itself and allow it to manage the log-in names and passwords, or Active Directory accounts, which is a log-in system typically used by larger organizations and companies. In order to gain access to the server's content, I'll make sure that the authentication drop-down is set to Windows Authentication.
That'll automatically fill in my current log-in information from my Windows account, which should give me the administrative permissions that I need. Because this is coming from my Windows log-in, I don't actually need to type in the user name or password here. Let's go ahead and make sure Windows Authentication is selected here, and press the connect button. Once I'm authenticated, I'll be presented with the main interface in Management Studio. You can expand the left hand window, called the Object Explorer window, by dragging it over here on the right hand side. If you make it wide enough, you can see the server name here as well as the user name that we're currently logged in under.
You can see that I'm currently logged in underneath my Adam account. Now we'll take a look at the Management Studio interface more in-depth in the next chapter, but for now, go ahead and come down into the Security folder and press the plus button to the left side of that. And then go ahead and expand the log-ins folder. Inside we'll see a listing of all the user accounts that are currently established on the server, including my Windows log-in account that I'm using now. One other account that I want to draw your attention to is the SA account. SA stands for system administrator, and you'll note the small red X icon, which indicates that it's currently disabled.
We can enable the system administrator account by right clicking on it and choosing properties. Then I'll select the status page from the left hand menu. Underneath settings, I'll come down to log-in, and I'll change its option to enabled. Then we'll come back over to the general page and we'll give the system administrator account a new password. This can be anything that you like but for security purposes, it should follow the strong password guidelines of including upper-case and lower-case letters, digits and symbols, and be more than eight characters in length.
Let's go ahead and confirm the same password, and then we'll come down and press the Okay button to save those credentials to the SA account. Now if your icon didn't automatically update like mine did in here, it still has that red X, go ahead and right click on the log-ins folder, and choose refresh. Now that red X should disappear and we should see that the SA account is now an active log-in. There is one additional change that we need to make to the server, and that's to enable SQL Server Authentication mode, which is disabled by default when SQL Server is installed, using the basic method that I used previously.
To do that, come up to the top of the Object Explorer window and right click on the Server Instances name. Then we'll come down to properties. Then we'll come over to the security page and underneath Server Authentication we'll change its option to include SQL Server and Windows Authentication modes. I'll make that change and press okay. That'll bring up a window saying that some of my configuration changes will not take affect until SQL Server is restarted. I'll go ahead and say okay to that, and that'll bring me back out to Management Studio. In order to restart the server, I could go back into Configuration Manager that we saw in the last movie, but I can actually do that right here inside of Management Studio.
I'll right click on the server instance name here at the top and come down to restart. That'll bring up a couple of prompts. We'll allow it to make changes to my device. And then I'll say yes to this window and it'll go ahead and stop and restart the server. So now we have two methods to log in. Let's try out the SA account. Go ahead and press the plug icon with the red X to disconnect from our server instance. Then I'll press the other plug icon to reload the connect to server window. We'll connect to the same server instance, but this time I'll change the authentication mode from Windows authentication to SQL Server authentication.
The log-in name will just be SA, and the password will be the one that we typed in. I'll press the connect button, and if everything went in correctly, it should reconnect me to my SQL Server instance underneath the new user account of the system administrator. We can see that by reviewing this first line here in the Object Explorer and at the very end it says SA. So that's how you'll gain access to the server. You'll either use your current Windows or Active Directory account, or have to establish users within SQL Server. Using strong passwords and limiting user accounts can help protect the server from unauthorized access in order to keep your data secure.
▶️ DTube
▶️ IPFS