Databases: Fast and Dirty Install of MS SQL Server on Windows
A portable development environment for Microsoft's flagship database
Audience: Microsoft database developers, architects and administrators, and development and engineering managers
Contents
Introduction and Executive Summary
Downloads and Installation
Updates
Starting, Stopping and Restarting the Server
Connections
Initial (Simple) Configuration
· Setting “Mixed Mode” Logins
· Strong Password for the sa Account
Conclusions
Footnotes
· LinkedIn
· Substack
Introduction and Executive Summary
A customer of ours runs an all Microsoft shop and needs a database for various operations, including reporting. Developing solutions for that customer requires us to implement a similar environment, at least as far as the database back end is concerned.
Previously, we provided an overview of MS SQL Server in general, and use cases where it was desirable and where not, and also outlined the major client side software needed to complement that environment on our Windows workstation. Portability concerns have now led to our need to also install and configure MS SQL Server on our Windows workstation as well.
The purpose of the present article is to document that install and the bare-bones configurations needed to rapidly create our development environment. Our current needs are rather simple: we just need a sandbox development environment to familiarize ourselves with the system (our primary expertise is Oracle), and begin the long process of developing a database system, from scratch, that will evolve over time into our development environment for our customer.
The current treatment will focus on only two components: SQL Server itself and the SQL Server Management Studio (SMSS). SSMS is used to administer, manage and develop databases on SQL Server and can be considered a remote client of the database, regardless of where installed.
The procedures described here are very easy and fast to perform, allowing even novices to the platform to implement their own database systems. Development and engineering managers can benefit, too, since having a model system they control allows a much greater familiarity with the systems their reports are using every day, and provides an independent way of personally checking on various system properties and behaviors. We needn’t belabor how important this can be for conscientious management of technical workers; if they know their boss can check up on their claims, they become careful, and likewise conscientious.
This article, part of our SQL Server series, will be continued with further, usually brief, articles on the RDBMS, indexed at our lead-off article Databases: Introduction to Microsoft SQL Server. Note that these articles are written by a long-term (since 1999) Oracle database specialist, likewise needing familiarity with the Microsoft server, and so may be considered authoritative from the standpoint of enterprise-level database architecture, administration, development, maintenance and practice.
Downloads and Installation
There are a few considerations necessary, prior to installing the database and related software. We covered these in detail in our client-side article, and so only mention them in passing here.
Licensing, in particular, is free for a Microsoft registered developer, and so a prospective developer need only create such an account in order to download the software. Storage space will need to be ready; the server requires about 6 - 8 GB, including space for the database; SSMS needs about 500 MB. Thus, one will need about 7 - 9 GB of free space to be on the safe side.
Once these items are settled, the software can be downloaded and installed. We supplied links for downloading and documentation in the previous article. We chose to go with completely default settings to make the install as simple as possible.
Installation of SQL Server and SSMS presented no problems; simply invoke the two installer programs in succession and follow the install wizards, exactly as any other Windows programs. We chose to install the database first and then SSMS; we didn’t try the other way but suspect the order of install doesn’t really matter.
Updates
Once installed, the documentation recommends upgrading SQL Server to the latest patch version. This can be done most easily by the following method.
Enable Microsoft Update on your system:
Go to Control Panel > Windows Update.
Click Change Settings and ensure "Give me updates for other Microsoft products when I update Windows" is checked.
Once this is done, one can use the Windows Settings, go to Windows Update, start an update, and SQL Server will show up as one of the software updates. Update the system and SQL Server, reboot, and the update is completed.
There are other update methods available. These will be covered in a future article specifically on that topic, including automated updates. Given Microsoft’s history and proclivity for reboots, we can advise readers to expect that a reboot will be required for updates.
Starting, Stopping and Restarting the Server
As with upgrades, there are multiple ways to start, restart and stop the server. We’ll cover only the simplest here, and reserve the other methods for a future specific article on the topic.
Microsoft computers run multiple services, which can be seen and controlled via their Services utility. To invoke it, simple go to the search box in the taskbar on the bottom left of your screen and type “Services”. Administrator access may be required. A window will come up with a long list of available services on the computer and the status of each.
Scrolling down, one will find SQL Server (MSSQLSERVER). After install, SQL Server usually shows “Running” as its status by default. Right-clicking on the entry brings up a menu with multiple options: Start, Stop, Pause, Resume, Restart. Controlling the status of the server is just that simple - choose one of the menu items, and (of course) let it finish the operation.
It is frequently the case that making changes to the configuration of the database requires a restart of SQL Server, and the Services utility makes this easy and fast. For the present, this is our recommended way to control the server status.
Connections
We assume both SQL Server and SSMS have now been installed, and SQL Server is running. The next important activity is connecting to the server via the SSMS client. For our system, simply typing “SQL” in the search bar showed SMSS as the top choice. We typically create custom toolbar menus in Windows for frequently invoked programs, allowing rapid access to our most important applications, and can recommend the practice.
Once SSMS is up, an Object Explorer pane on the left hand side is visible. Immediately below the Object Explorer is a Connect bar. Trying to connect the first time may bring up the following error:
TITLE: Connect to Server
Cannot connect to <server name>.
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver--2146893019-database-engine-error
The certificate chain was issued by an authority that is not trusted
If this error shows up, click on Connect (using the Database Engine drop-down menu choice), and a Connect to Server window pops up, with several tabs. The Login tab should show the default connection information. The last tab is Additional Connection Parameters, with a single text box under the heading “Enter additional connection string parameters (will be sent in clear text):”. In that text box, enter:
TrustServerCertificate=True
and then click the Connect button. If the connection works, one will see a tree of items, similar to the left pane of the Windows Explorer, indicating successful connection to the database.
Otherwise, additional debugging will be required. To make that as painless as possible, use your favorite AI. In our case, we used Microsoft Copilot, reasoning that, as a Microsoft product, it had better know its way around other Microsoft products, which turned out to be true in this case.
Initial (Simple) Configuration
Since we are going for a rapid system preparation, which can and will be stepwise improved later, we can concentrate on only a very few items needed immediately to configure our server. The absolute minimum criteria we need are:
"Mixed Mode" authentication option (Windows and SQL Server authentication).
Strong password for the "sa" user.
The “sa” user stands for system administrator. Mixed Mode authentication allows logins via our Windows user, which is convenient and secure enough, so long as the Windows system is sufficiently secured.
We covered several of these issues in our Windows Security Sources and Windows Security 101 articles previously. Fortunately or unfortunately, for our readers and ourselves, the security of our computers is and always will be our own individual responsibilities.
Setting “Mixed Mode” Logins
The top level object in the left pane of SMSS is the database, usually “<server name> (SQL Server <version - server name>”. Right-clicking on this object brings up a Server Properties - <server name> window. In the top left-hand corner is a section Select a page, with several choices below. Select Security to display a list of security choices on the right.
The first choice (radio buttons) is between Windows Authentication mode and SQL Server and Windows authentication mode. The latter is our desired Mixed Mode, so select it and hit OK. Then, go to Services as specified above and restart SQL Server. This completes the Mixed Mode configuration task.
Strong Password for the sa Account
We recommend using a password safe such as KeePass, Password Safe, 1Password or others as desired. A typical computer user will, over time, accumulate passwords for possibly hundreds of accounts of varying types, and so it makes a great deal of sense to use the same password safe, even for local software such as SQL Server in the current case, allowing long, randomized passwords, that are nearly impossible to guess nor break using brute-force methods.
We tend to use 20 character, randomized character passwords that include lower and upper case letters, numbers and special symbols. However, it should be noted that not every such password is friendly for every application. Oracle database, for example, has issues with certain characters in passwords, and SQL Server does also, but a different list. To keep it simple, a mix of uppercase letters, lowercase letters, numbers, and commonly supported special characters like #, $, %, ^, &, *, or _ will typically work well with SQL Server.
We leave the generation of a random password and storage in some kind of password safe up to our readers, except to note that, once generated, the password should be examined and perhaps edited according to the above rule. Store the password in the password safe first, and then change the SA password in the server.
Here’s how. To set a strong sa password:
Open SSMS and connect to your SQL Server instance.
Navigate to Object Explorer > Security > Logins.
Right-click on the sa account and select Properties.
Under the General tab, enter the new password just created.
Click OK.
Interestingly, the sa account is disabled by default. In order to test the change in password, it has to be enabled, even if temporarily.
NB: We don’t believe there is much risk in enabling it after changing to a strong password, and will leave the issue of changing to an entirely different system administration account for a later article. The choice is up to the reader: some administrator account is necessary, but it needn’t be the default sa account.
To (temporarily) enable the sa account, go to SSMS → Security → Logins, and right-click on the sa account. Notice the red “x” on the icon, indicating the account is disabled. A window pops up, and in the top left pane Select a page, there are several choices, the last of which is Status. Clicking on this choice displays a Settings pane on the right side; the last item on this pane is Status. There is an item there, SQL Server authentication, with a checkbox Login is locked out. Uncheck that box, and click OK.
When we tried this, logged out and then in, the sa account was still disabled. The next step was to log back in using Windows authentication, pull up a query window (immediately under the menus at the top, labeled New Query, and enter:
alter login sa enable;
go
then click Execute immediately below New Query. Inspection of the sa account at Security → Logins now shows it enabled. (Comment: as a long-term Oracle DBA / Architect, this “go” statement feels superfluous and unnecessary. In Oracle SQL*Plus, the semicolon signals the end of the statement, and executes upon hitting ENTER.)
We then logged out and tried again, using the SQL Server Authentication method, noting that the above additional connection parameter TrustServerCertificate=Truewas needed again, and we needed to input the Login as sa and Password as chosen. The login, using SQL Server authentication, was now successful.
Back to Contents · Back to Top
Conclusions
With the successful completion of these two tasks, we now have a working and secured SQL Server environment. In the coming articles, we’ll systematically explore progressively more advanced administrative and development details, always from the viewpoint of an Oracle expert seeking to reproduce as much of the extremely powerful functionality of that king of all databases as the technology allows.
Back to Contents · Back to Top
Footnotes
Thank you for reading this article!
More information about Overlogix can be found at Welcome to Overlogix!
We currently publish on both LinkedIn (general interest articles, summaries, TL;DR’s: easier and faster to read) and Substack (in-depth articles, how-to’s, technical studies and new approaches to business).
Back to Contents · Back to Top
LinkedIn
· Why hire me? The elevator pitch
· Introduction: Welcome to Overlogix!
· The Overlogix Sunday Times Our newsletter, with occasional specials, published roughly every two weeks.
· Master Index All our articles can be found from here in two clicks.
· The Overlogix Table of Context All Overlogix articles in reverse chronological order
· Applied Artificial Intelligence: Index of Articles One of our specialties is Applied AI. This index lists all relevant articles on the topic, in reverse chronological order.
· Applied AI: Stories in the News Our semi-permanent, curated listing of interesting and important news from the world of artificial intelligence, from many different sources.
· Index: Getting a Job Up until recently, getting a job, much less a good job, has been a nightmare for most job seekers. We publish articles on how and why this is so, and what job hunters can do to find the perfect job for them. We also supply credible external resources, so people can consider their alternatives.
· Starting a B2B Business For everyone who can, we heartily recommend starting your own business. The tools are there, and there has never been a better time to do it.
· Building Our Own Robot We’re automating Overlogix from the start, and this series of articles tells exactly how we are doing it.
· Rebuilding the Linux Server: Index of Articles Running AI on your own machine (recommended) requires a modern, up-to-date operating system, and often a lot of additional software infrastructure. This series, dedicated to exactly that sort of system administration, details what we have done to build a powerful server that runs both databases and artificial intelligence, locally.
· The Gospel According to ChatGPT Conversations with various AIs and additional articles on the various challenges associated with actually making profitable use of artificial intelligence.
· TL;DR: Index of Fast Reads Brief, fast reads on various topics in artificial intelligence. If you are a beginner at AI, or a busy human needing fast and factual explanations of complicated technical topics, this is the place to start.
· TL;DR: Overlogix Artificial Intelligence Mini-Wiki Same Fast Reads as previous but arranged in a mini-wiki format some folks may like better.
Back to Contents · Back to Top
Substack
· Welcome to the Overlogix Substack
· Overlogix: Table of Context Index to our Substack articles arranged by topics.
· Criteria for Paid Content Rules for what goes behind our paywall.
Curated IT and AI Sources Annotated links to sites and YouTube channels we think are valuable.