Welcome to the first day of SQL University. Today we’re going to be talking about basic tools you’ll be using as a database administrator (DBA).
Throughout our lessons you will notice I will be linking heavily to SQL Server Books Online. Books Online is the official documentation for all things SQL Server. This is important to know as many administrators and developers refer to this documentation on a daily basis as well as in everyday conversation. You can access Books Online in one of two ways. One is directly via the website on MSDN or you can actually download Books Online (Click to Download Latest as of 9/23/09) so that you can access and refer to the documentation even when no network access is available. While downloading it for offline use can be beneficial (and portable) be aware that Microsoft does update Books Online with new information which means you would have to download and install the latest version of Books Online when this occurs. The good news is that when you use the local version of Books Online it does ask you up front if you want to use the internet as the first point of reference. Another advantage of having Books Online locally installed is that you can bookmark topics and searches so you can save time if you find yourself referring to a certain topic (which I can almost guarantee you will!). That being said make sure you explore the various links given to fully get the most out of the content delivered here at SQL U.
The most basic tool in the SQL Server toolset for an administrator or developer is a management graphical interface called the SQL Server Management Studio (SSMS). SSMS is where you can access, configure, manage and administrate your servers. The following video walks you through the basics of SSMS so you can become familiar with it. Before you watch the video there are a few things you need to know about SQL Server.
Authentication
In order to connect to a server or database you need to provide it valid credentials. This method is referred to as authentication. SQL Server recognizes two different types of authentication: Windows authentication and SQL Server Authentication. Windows authentication (sometimes also referred to as Integrated Security) is when you provide SQL Server Windows account credentials. This can be either a Windows domain account (i.e. domainusername) or a local Windows account (i.e. local-machineusername). By default Windows authentication is the default authentication method selected when you open SSMS, and of note, is also more secure. We’ll get in to the hows and whys of that in another class. When you open SSMS, the Windows credentials for the account you are logged into the machine as will automatically pass to SSMS. For instance if I’m logged into my computer as a user called JSEGARRA, that is on a domain called MSDOMAIN, SSMS will open and you will see in the box for username (will be greyed out) MSDOMAINJSEGARRA.
The second method of authentication is SQL Server Authentication (sometimes also referred to as just SQL Authentication). This method of authentication is useful for instances that, for whatever reason, do not have access to a Windows domain account or just a domain in general. SQL accounts are created and kept within the database instance itself. An example of a use for this type of authentication method would be a database server that resides outside of a company firewall so that the public needs to get to it. Typically these servers are kept in what’s called the DMZ (demilitarized zone), which is an area that belongs to the company but is segregated from the internal network for security reasons. Since the DMZ is outside of the normal network you wouldn’t be able to authenticate with a domain account so instead we use local credentials like a SQL account.
Best of Both Worlds
For those curious, yes you CAN have both Windows authentication and SQL authentication enabled on your database server. This mode is called Mixed mode since you’re mixing both types of authentication methods. Be aware, however, that this increases your attack surface as you’re opening more holes to access your database server. Microsoft best practices recommend using Windows authentication for security reasons (account is managed at domain level, leverage AD groups, etc.).
Video: Walkthrough of SSMS pt 1. (9:02)
Warning: Video is hosted by YouTube. If you cannot see it your company might be blocking that site. My apologies, I will have an alternative method available in future.