Mark Minasi's Windows Networking Tech Page
Issue #48 July 2005
To subscribe, visit http://www.minasi.com/nwsreg.htm. To unsubscribe, link to http://www.minasi.com/unsubs.htm. To change e-mail address, switch between HTML or text format, etc., link to http://www.minasi.com/edit-newsletter-record.htm. Visit the Archives at http://www.minasi.com/archive.htm. Please do NOT reply to this mail; for comments, please link to www.minasi.com/gethelp. Document copyright 2005 Mark Minasi.
What's Inside
- News:
- Tech Section
- Summer Reading To Make You Secure
- The Accidental DBA: Everything You NEED To Know About MSDE
- Installing Web Data Administrator, the Poor Man's Enterprise Manager
- Conferences
- Bring a Seminar to Your Site
News
This month brings a newsletter that's almost twice as large as the previous record-holder, but trust me, it's worth it and I think you'll enjoy this issue. The size of this thing is why I didn't get a newsletter done the past two months.) What's it all about? I announce an online free Web course on SP2/SP1 from TechTarget, review a couple of must-have security books, but mostly this newsletter is about MSDE, Microsoft's free database engine.
You see, it seems as if everything interesting that Microsoft releases these days needs a database engine, and I hate to shell out a bushel basket of Benjamins for a copy of SQL Server 2000. So I could install MSDE, but, well, it seemed like it was mostly a hobbled version of SQL Server 2000, with the GUI administration tools removed. Yuck, thought I, that's all I need -- one more set of administrative burdens and potential security holes. Memories of SQL worms past have made me leery of putting extra SQL servers on my systems, even if they are free.
But this month, I was tempted to name this newsletter "Dr. Strangelog: how I learned to stop worrying and love MSDE." Yes, SQL Server Express 2005 is coming in November, but I'll bet you've got a bunch of MSDE boxes around and they'll be around for a while... so curl up with a warm browser and learn how to bend MSDE to your will without a GUI. And, then, for dessert, I show you a free Web-based GUI for MSDE... that is, if you can get it to install, and the article will show you how.
Tech Section
Summer Reading To Make You Secure
Before I dive into the meat of this newsletter, let me pass along two suggestions for a bit of network security summer reading: Hardening Windows Systems by Roberta Bragg, and Protect Your Windows Network by Steve Riley and Jesper Johansson.
When waging war with an enemy, you must employ great tactical and strategic resources. Running a network connected to the Internet is war, war against a host of criminals who want to steal financially valuable information and antisocial dweebs who want ... um, actually I'm not sure what they want, except I guess to validate their parent's-basement-dwelling existences by feeling better about poking around your network.
In any case, good strategy and tactics resources are hard to find. That's why I'm happy to tell you about Roberta, Steve and Jesper's books. I guess I should put in a disclaimer or two: first of all, the three of them are my friends, but that's not why I like these books. Second, I don't have any monetary interest in them, except that I might make a about twenty-five cents if you buy the books by clicking on the hyperlinks in the first sentence of this section. (Believe me, the Amazon Associate program ain't what it used to be.)
Have you ever downloaded and tried to read the various "hardening" white papers? They're at worst dreadful and disorganized, and well, at best not anything you'd want to flip through. Roberta has collected all of the essential group policy and Registry tweaks and hacks that you can do to tighten up your systems against attackers both on the far and near side of your firewall. And, unlike most technical security resources, she doesn't say "here are the 2003 and XP hacks, too bad if you're still running NT 4 or 98." Nope, wherever possible, Roberta offers the best advice possible for securing even the unsecurable -- and here I'm talking, of course, about Windows 9x and Miserable Edition. (I have to give her credit for patience in digging up this detail; I must admit that when asked how to secure 98 as well as XP I am tempted to suggest that the questioner pick up the Microsoft security patch that makes 98 as secure as XP. When the questioner, intrigued by this, asks the name of the patch, I want to say "the XP installation CD," but I've managed to avoid doing that yet.) Furthermore, the text carries throughout it the ring of authority -- you get the no-doubt-accurate impression that Roberta actually sat down and tried every single one of these things, unlike, well, so many Web pages and white papers. She's pretty diligent about telling you that applying security fix X will tend too break operating system feature Y, and therein may lie some of the book's greatest value -- she'll save you time. Yes, everyone's network is different and so you shouldn't blindly apply changes to the production network without complete testing, but testing takes time, and she'll save you time. So let's see... it costs 40 bucks, you pay your network types such-and-such per hour; let's say that it saves you three hours testing a given set of hardening configurations -- you're ahead of the game, and that's just by the time you've gotten past the first tweak.
The table of contents is roughly arranged, as is the case with Steve and Jesper's book, along the lines of the OSI model -- harden the physical stuff, harden the protocols, harden the apps. Each chapter contains a mix of short essays followed by the specifics of the hardening advice -- you're rarely far from a solid tip, and the combination of short exposition and bullet points means that this book is eminently "browsable." This is the Windows security tactician's handbook.
Steve and Jesper's book is, in contrast, a series of longer but still quite readable essays. Some are high-level strategic pieces like "how to develop a security policy," or "why it's worth bothering with a perimeter firewall despite its inevitable leakages," and others are pieces on the specifics of patching... but they don't just talk about patching systems, they talk about patching people -- how to implement security so that people won't spend their time circumventing that security. Like Roberta's book, this book resounds with that ring of authority: Steve and Jesper have been helping big outfits secure their networks for a long time, and have had decades to think about what kind of security makes sense, and what kind does nothing more than annoy people without making things more secure -- "security theater," they label it. (A great phrase, and one I intend to propagate.) But the "enterprise" focus of the book doesn't mean that small shops shouldn't buy it; first of all, there's a lot of good advice about securing systems that I'd say applies equally to the tiny and the gargantuan, and second, they've got a chapter just for small businesses.
The book starts out with the anatomy of a hack, where they describe how a mythical hacker could exploit just a few small and quite probable mis-configurations to ultimately gain control of an entire domain. And if, after reading that chapter, you can resist putting down the book and checking a few things on your network, then I'd be quite surprised. Then they move to policies, and here I do not mean Windows-type group policies, but instead the written-down type, the "if we catch you erasing the security logs then we fire you" kind of policies. But you're not in for a dose of "network Nazi" training; I think I recall at least three times in the book that Steve comments that our job is to let people get to the resources that they need to keep the organization running. From there, they move on up an OSI-like hierarchy, like Roberta.
Now, let's be clear -- security isn't a particularly fun topic. But Steve and Jesper keep it readable with anecdotes from their travels that are sometimes funny, sometimes frightening, but always enlightening. Like Roberta, Steve and Jesper spend a fair amount of time teaching and speaking on the subject; perhaps that's why the three of them are so readable. But, better than that, the three of them have, again, all been in the business long enough not to be swayed by the "security panic meme of the day" -- like all good technical books, these contain an excellent word-to-wisdom ratio. (I think that Steve and Jesper's book contains a particularly high level of wisdom in its citing of a source on page 364, but that may be a case of personal bias.)
I must, unfortunately, ring a minor sour note not about the books but about the way that their publishers fell a bit short in taking care of a very important part of any book: the index. I strongly recommend that you keep a highlighter and some Post-its handy when reading either book, as the indexes are terrible. (Authors have no control over indexes.) For example, Roberta has what has to be the most useful advice I've seen on handling null sessions, including a list of the named pipes which allow anonymous connections by default but which can be disable ... and I can't find it again, thanks to its index. It's no big deal, as I enjoy paging through the book reviewing its advice, and now that I've always got a Post-it pad always nearby when reading the book, I know I'll find it again. Steve and Jesper's book has the same problem; for example, they offered some really useful advice about de-fanging SQL Servers -- advice I stole for this newsletter -- by disabling something called "xp_cmdshell." Looking for xp_cmdshell or "SQL injection" in the index yielded nothing. As an author whose books are also sometimes indexed badly, they have my empathy. Pick these up today and I promise they'll be dog-eared in no time!
You can pick 'em up at Amazon at the hyperlinks above, or at your favorite technical bookseller. Do it, and your un-hacked data will thank you.
The Accidental DBA: Everything You NEED To Know About MSDE
Introduction
I've never hankered to become an expert on databases, database structures, SQL, or SQL Server 2000, nor do I claim to be one now. But there are times that I have to run a database server and I'll bet I'm not the only admin who's ever been in that position. Nowadays it seems that every Windows server admin has to run a database server, even if we don't use traditional database-driven applications. I say that because more and more essential network utilities -- Microsoft's new Windows Server Update Services, for example -- simply cannot run without a database server. And as you know, database software can be expensive. Particularly Microsoft's database server, SQL Server 2000.
Now, Microsoft knows that many of us either can't afford or don't want to buy a full-blown copy of Microsoft SQL Server 2000 to run those utilities, and so they offer a free no-frills version of SQL Server 2000 called Microsoft SQL Server 2000 Desktop Engine. That mouthful gets abbreviated to "MSDE 2000," or, more succinctly, "MSDE." It's nice that they do that, else we couldn't have those nice network utilities that need a database like locker rooms need Tinactin. But once you've got a database engine running on one or more of your servers, then somebody's got to play database administrator -- 'DBA" in the inevitable acronym -- and maintain the server. Somebody's got to know things like how to back up its databases, patch the engine, manage accounts and passwords and other security items, move databases from one MSDE machine to another and so on. Somebody. Like, maybe, you and, well, me. I call folks like us "accidental DBAs." And believe me, friends, figuring out what a DBA's got to do and how to do it can be time-consuming and frustrating. But it needn't be.
This month, I intend to save you time and frustration by explaining the whole mess. First, I'll give you a bit of background about why you need a database server, and tell you how MSDE can fill that bill. I'll tell you what MSDE can and can't do, and that you may have MSDE running on some of your systems right now without even knowing it -- just think, you may already be an accidental DBA! We'll then look at the security problems that MSDE has posed in the past, which is in truth the main reason that I had to learn all of this, and tell you how to easily reduce your security risks. After that, you'll see where to find MSDE, and how to install it. Once you've got it running, then we'll want to play with it a bit, and so I'll take a short side-trip to play database designer, programmer and user, where I'll show you how to create and populate the world's smallest MSDE database, and how to put it to work with a few queries. And while we're doing that, I'll get a chance to show you MSDE's main administration tool, a nifty little program named osql. Armed with a knowledge of osql, we'll then tackle the how-tos of what I think are the things that every accidental DBA should know. So let's get started...
We're All DBAs Nowadays, At Least A Little
Over the years, Microsoft and others have released a number of irresistibly useful tools that are wonderful in that they solve some important network administration problem. Those tools are free downloads (which is nice) but are daunting to a non-DBA like myself because they require a working, living, breathing, functioning Microsoft SQL Server 2000 to run (which is scary). In particular, I'm thinking of:
- WSUS (the recent replacement for Microsoft's SUS patch management tool)
- Ultrasound (a neat FRS monitoring tool)
- Application Compatibility Toolkit 4.0, a free Microsoft tool that helps solve problems running software written for an earlier version of Windows that has troubles running on modern versions of Windows
- the upcoming Audit Collection Services, a must-have tool that will centralize security logs from your Windows systems on a central --you guessed it -- SQL Server 2000 machine
- the Resource Kit has had for a while the the "cconnect.exe" program, a tool that lets you keep Windows users from being logged onto the domain on more than one system at a time
- A number of nice, free Web-based apps need databases; for example the online forum on my Web site is built on Snitz, a set of VBScript-based Active Server Pages that need a SQL server, and MSDE can do the job nicely.
Each of these utilities require a real-live Microsoft SQL Server 2000 or they can't run, so don't even think about trying those tasty tools without first learning DBA basics. (That's why you're reading this.)
Worse yet, some applications make people into DBAs without even knowing it. Two examples are Veritas's Backup Exec and Microsoft's Visio 2003 Enterprise, apps that need a database and package MSDE with them, installing it automatically. I don't mind that the apps need a database, but I do mind that they install without first explaining to the poor fool installing them that the admin shouldn't even think about installing the apps without first learning those DBA basics that I've been talking about here.
MSDE Strengths and Weaknesses
Hmmm... so I'm suggesting that you use this MSDE thing instead of SQL Server 2000? Will we lose something by going with MSDE? Sure, some things, but we mostly won't care. Compare MSDE to SQL Server 2000, and you'll see that MSDE has really only one advantage over SQL Server 2000: MSDE is free. Sure, it's just one advantage... but it's a big one.
If we apparently all need at least some of the services of SQL Server 2000, why not buy it? The usual reason -- cost. There are a ton of ways to buy SQL Server 2000 -- by the CPU without client access licenses (CALs), by the server with CALs, or if you're a Small Business Server owner then it's built into that. But no matter how you slice it, SQL Server 2000 will run you some money -- for example, to put the lower-functionality version, Standard Edition, on a one-processor system will cost just under $4,000. So a free but reduced-function copy of SQL Server 2000 -- that is, MSDE -- could well be useful.
Well, then, what does MSDE lack in comparison to SQL Server 2000? Basically three types of things. The first type of MSDE deficiency I'll call "enterprise and developer database limits that we won't miss at all." They are
- No OLAP support. OLAP is used in for data warehousing in truly huge databases, and we're not going to be doing any of that.
- No Index Tuning Wizard. This helps developers write faster-running code. We ain't no developers, so no loss there.
- No database server failover support. Useful in clusters... but we're not going to need to cluster a little MSDE database that just holds some result data from WSUS, ACT and the like. (At least, I hope not. If you've got that much money to spend, then think about a patching solution that costs money and does more.)
- No import and export wizards. SQL Server's designers want to make it easy for you to move your databases from other database engines, including Microsoft's own Access database software. We'll be using MSDE in brand-new databases, so we won't have to import databases from other locations. Note that this doesn't mean that we can't take a database from one MSDE system to another, as you might if you were to move WSUS (to continue my ongoing example of a good use of MSDE) from one server to another -- that's easy and we'll see how to do that later.
- MSDE will only employ two CPUs, no matter how many your system has. One of SQL Server's big strengths is that it's extensively multithreaded and, unlike most apps, it'll gladly chew up all the CPU power you have, no matter how many chips that comes in. But, again, we're using MSDE to do things like collect a bunch of result codes from patching (as in WSUS), receive short event log entries (as in ACS), store a list of what applications sit on a computer (as in ACT 4.0) and so on, so I don't think a two-CPU limit's really going to hurt us. It's not like we'll be analyzing telephone billing databases or the IRS master file. (Now, that would be a taxing task. Sorry, couldn't resist.)
- MSDE will only use 2GB of your server's RAM. Again, for the size of things we're going to working with, this will not be a constraint and in fact may become a benefit. You see, in my experience SQL Server and MSDE are truly avaricious in nature RAM-wise -- systems running SQL Server often find that SQL's stolen much of the system's RAM.
No problem with any of that, even if we were going to try to be big-time developers. Second are the "things we might miss... but we can work around easily" limitations. They are
- No "SQL Books Online." These are a bunch of fairly useful documents about SQL Server 2000, from installation to operation and maintenance to database design to you-name-it. Documentation's nice, y'know? Bummer... but we can work around it, as Microsoft has put SQL Books Online available for download. Go to http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp and download "SQL Server 2000 Books Online (Updated 2004)." There are also some nice Web resources that I'll mention through the rest of this article.
- No Enterprise Manager. Double bummer. For those who don't know SQL Server 2000, EM is a nice GUI that lets you do just about anything you'd need to do by just clicking, which is particularly nice for us infrequent DBAs. Whether you want to create and delete databases and tables, adjust SQL Server/MSDE's hunger for RAM, back up and restore databases, query tables, EM does it all. So what do we do? You've got a couple of no-charge options. First, I'm going to show you how to do everything that EM does that we need with a tool called osql. Second, there is a Web-based SQL Server manager called the "SQL Server Web Data Administrator." Google that phrase and you'll find where to download it. It's a nice GUI way to control some of the things in your MSDE system, as it lets you manage user accounts and passwords, import and export databases, and create and edit databases. But to make it work, you've got to install Internet Information Services (IIS) on your MSDE system, and having a lot of Web servers running worries some people. You can alternatively forgo IIS and install an open-source .NET-based Web server called Cassini and run SQL Server Web Data Administrator. But whether you go with the osql answer or SQL Server Web Data Administrator, you can get everything done that you need to do, without Enterprise Manager.
I want to stress that last point: you don't need Enterprise Manager to make MSDE work. Yes, EM makes life easier, but it's not necessary. I do a ton of MSDE management via osql, and soon you will too. (And sure, EM will control an MSDE with no muss, fuss, or greasy aftertaste, and I've been tempted to "borrow" a copy of Enterprise Manager from a friend with a full-blown copy of SQL Server 2000 now and then. But, heck, it's not nice to steal software and, besides, getting caught at that can land a person in jail for up to five years and get him or her fined up to a quarter of a million bucks!)
Finally we come to the third set of MSDE limitations: the "gosh, I wish it weren't so" limits. There are three of them.
- Maximum database sizes: MSDE will not allow any single database to grow beyond 2 GB in size. Specificially, databases live in at least two files -- the database file and the log file -- but the 2 GB limit only applies to the database. You can spot database files by their extensions, MDF and NDF. (An NDF is a "secondary" database file. You might have one if you wanted to extend a database beyond the capacity of the disk it's sitting on, among other things. You probably won't run across them in MSDE work, but it's possible.) Any given MSDE database engine can host as many databases as you like, but none of files can individually exceed 2 GB. In other words, suppose I set up an MSDE system containing a sales database that was 1 GB in size and an engineering database that was 1.5 GB in size. Although the sum of those two databases is 2.5 GB, that'd be fine -- MSDE would only squawk if either of those databases grew to 2 GB. Is this going to be a problem? Well, it might be for the Audit Collection Service (ACS), which lets you aggregate the contents of any number of computers' Security event logs into a single MSDE database. Let's see... my servers can each generate about 10 MB of Security events in a week. Figure that's about a half gig per server per year. Multiply that by the number of servers and workstations and yeah, we could probably get to two gigs in no time. Time will tell and, besides, there may be a workaround called WMSDE, a version of MSDE without the 2GB database limit -- but more on that later.
- The "throttle:" MSDE's most severe limitation may be its "throttle," a feature that slows MSDE down if a lot of people access an MSDE server at the same time. Microsoft says that MSDE slows itself down whenever more than "eight operations are active at the same time." That's not that helpful because the phrase "operations" is a bit nebulous. It does not refer to the number of people connected to the database engine, as most of the time that you're connected to a database engine you're not asking it to do anything. Some people explain that this is roughly equivalent to either five simultaneous "workload batches" -- another SQL term -- or 25 concurrent Web clients. I'd honestly like to explain that in greater detail, but I'm not a SQL programmer. To get more of the ugly technical details, Google "The SQL Server 2000 Workload Governor" and you'll find an MSDN article on it. (Although if you're not a SQL developer then it may not mean much, unfortunately.)
- SQL Profiler: this is a neat troubleshooting tool that lets you see exactly what commands the SQL engine is receiving. Nice for debugging and for checking out the security of SQL-based apps. But it's not a real problem... it's just a shame not to have it to play with.
Are either of the first two limits killers? Probably not. But what if they are? Then there are two options. The first is WMSDE. It's a version of MSDE without either of the two above constraints. You can't actually download it and play with it by itself, so I've got to rely on what others have told me, but apparently there's something about WMSDE that allows Microsoft to control what you use it with -- while MSDE's limited on control tools, you can build a database of any kind that you like, while I'm told that WMSDE doesn't let you do that; unlike MSDE, WMSDE has a fixed database schema. WSUS does, fortunately, offer WMSDE as an option, so size won't be a problem with WSUS's database. In any case, by the end of the year this will be academic, as SQL Server 2005 Express (SSX) will replace MSDE and loosen up the database size limits a bit -- SSX's limit is 4 GB -- but that's another newsletter.
Oh, and how could I forget the licensing issue? As of this writing, Microsoft makes MSDE pretty much available to anyone for any purpose, but there are a few specific gotchas. All of the examples that I'm discussing here are fine. But if you want Redmond's view of things, take a look at http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp, "Appropriate Uses of MSDE FAQ."
Is It MSDE, MSDE 2000, MSDE 2000 Release A, or WMSDE?
There are a few versions of MSDE and in any given document you may run across any of the four names above, so to avoid confusion, let me explain each one. "MSDE" is sometimes used as the generic name for the limited version of SQL Server that Microsoft's been offering since SQL Server 7.0. But some Microsoft documents also use "MSDE" to refer specifically to the version that is built from SQL Server 7.0.
"MSDE 2000" refers to the version of MSDE that Microsoft created by removing features of SQL Server 2000. The original MSDE 2000 -- which you can't find on the Web any more -- was based on SQL Server 2000 RTM (RTM = "Release To Manufacturing," the originally shipped version before the service packs and, yes, SQL Server has service packs and hotfixes of its own) with things removed. Microsoft later addressed some persistent MSDE security problems by creating another version of MSDE from from SQL Server 2000 with SP3 already installed; that version of MSDE is called "MSDE 2000 Release A," the most recent version that I've seen. I've already covered WMSDE.
Essential MSDE Security Concepts
Sometimes, security is a kind of side-show, a kind of afterthought. It shouldn't be that way, but it often is. With SQL servers, however, it can't be that way. Putting an unsecured SQL server on your network is a terribly bad idea, as SQL Server can, if not secured properly, provide a wonderful back door for bad guys to get into your network.
Okay, let's tell the truth: a lot of folks -- me included, once upon a time -- are just plain busy and so we just say "aw, heck, let's just do as this free-utility-that-needs-a-database asks and let it install MSDE; after all, the utility says it creates all the databases automatically. I'll get around to figuring out the DBA stuff later." Three bad things result from that: lots of database servers with no-brainer internal passwords, lots of database servers running a potentially weaker authentication system than the one built into Windows, and an Internet full of MSDE systems that had never been patched. So here's a cook's tour of the security problems and, more important, simple solutions in the SQL Server 2000/MSDE world.
Make Local SQL Accounts -- Especially sa -- Secure
The first source of insecurity on many SQL Servers was the existence of a lot of systems with a blank "sa" password. What's "sa?" SQL servers maintain their own sets of users and groups completely separate from the local SAM accounts that you find on Windows network members (NT, 2000 Pro and XP Pro workstations and NT, 2000, and 2003 member servers) and domain accounts stored in Active Directory. This adds an extra layer of potential security holes to your system, as everything that you already know about properly securing Windows local and domain accounts must be re-done with any account built on a SQL server.
Why would Microsoft let SQL keep its own accounts? Probably because back when SQL Server first came out, it was a small player in the database server world and, in truth, its host operating system NT was a pretty small player in the OS world. I recall that in the mid-90s many of my clients were people who used NetWare for file and print and had just a few NT 3.5 boxes on their network just to run SQL Server. Forcing someone like that to build an NT domain just to drop a few SQL Servers into their network would have made most of those folks decide to forgo SQL Server altogether. Then why do modern SQL Servers keep offering local SQL accounts? As far as I can see, they do it for the obvious reason -- backward compatibility -- as well as the fact that as far as I can see, there's no way to log onto a SQL Server in a workgroup from a remote system using osql, the utility that we'll rely heavily on, except with a local SQL accoung.
When installed, every SQL Server starts up with one initial account -- sa, the "system administrator." The sa account can do just about anything it wants on a SQL server and, worse, the sa can run something called "xp_cmdshell" which lets you run any Windows command on the SQL Server and return any resulting output to osql. So if someone gets your sa password and can see TCP port 1433 on most MSDE systems ("most" because the port may vary, as you'll read later), then that person gains a fair amount of control of whatever system's running MSDE.
Until MSDE 2000 Release A, the version based on SQL Server 2000 with SP3, then every version of SQL Server and MSDE created an sa account with a blank password by default. So guess what the sa password was for 99 percent of us accidental DBAs? You guessed it, blank. Sadly, some dirtbags exploited that in May 2002 to create a worm called "Spida." It would sniff around to find a SQL Server and, when it found one, it'd try to log on as sa using a blank password. If it succeeded, then it'd do nasty things like dump out the password hashes from the local system's SAM and e-mail them to some account -- the worm's creator, likely. (Yes, sa can do that kind of stuff on a system. Honest, friends, there are few things you can do to compromise your system's security more than by installing SQL Server 2000 or MSDE on that system, and then leaving the sa password blank.)
Needless to say, word of Spida led SQL Server administrators around the world to go "aaaauuuughhh! How do we change our sa password?" and the answer is, I'll show you a bit later. Meanwhile, Microsoft has changed MSDE's installation behavior so that MSDE 2000 Release A's setup program requires you to create a non-blank password for your sa account.
So our first MSDE security rule is: ensure that the sa password isn't blank. The latest setup program forces us to choose a non-blank password, and I'll show you how to do that in the upcoming section on MSDE installation. As promised, I will also show you how to change the sa password after you've installed MSDE.
Choose Windows Authentication Mode Over Mixed Mode
As I suggested earlier, the whole idea that SQL has its own user accounts is kind of troubling security-wise. It means there's another set of accounts to keep track of (how do create new accounts, give them permissions on things and so on), more passwords to worry about (how hard is it to crack an sa password, anyway?) and a host of other potential worries. With all of the work that Microsoft's done over the years to secure local and domain-based Windows accounts, why on Earth would we want to have to duplicate all that effort for local SQL accounts?
Well, the answer is that we wouldn't want to. And so in fact Microsoft's given us an answer, as SQL Server/MSDE also allows for something called "Windows integrated authentication" or, in some sources, "Windows authentication," which is almost certainly the right way to go when building and deploying applications. With Windows authentications, you don't use sa or any other local SQL account, but instead control access to database servers, databases, tables and the like with Windows-based accounts.
That means that MSDE and SQL Server 2000 support two different kinds of authentications -- SQL authentications and Windows authentications. Here's the potential payoff there: you might be able to tell your MSDE or SQL Server 2000 machine, "don't ever accept SQL Server authentications." By doing that, you could stop worrying about some future SQL worm that sneaks in through some as-yet-unknown hole in the SQL Server authentication code, and sa becomes mostly irrelevant.
Neat, eh? Yes, clearly. So we'd always use Windows authentications and we can forget about sa and its ilk, right?
In many cases, yes. But maybe not. You see, there are tons of already-written apps that were written around SQL accounts and in fact most were written with one account in mind -- the sa account. Such applications need the sa account active and knowledge of the sa password to work; they don't use Windows Integrated accounts and don't know about them, and instead require SQL Server authentication.
Now, understand, that's not a slap at the thousands of developers who built tens of thousands of SQL Server authentication-dependent apps. Until Spida hit in 2002, almost every home-grown SQL-based app needed to log on as sa to work, and for good reason: virtually every example piece of SQL code was built around an sa-centric database connection. (In fact most of the issue revolves around something called the "connection string," but that's a more developer-y topic than I want to get into in this article.) So you might find that whatever you're running that needs to sit atop MSDE might also need to be able to connect as sa, and needs SQL Server authentication to function.
But if your app is built to use Windows authentication, then you're in luck -- you can, as I'll show you when we see how to install MSDE, tell your MSDE server to only accept Windows authentication. More specifically, Microsoft refers to two "modes" for SQL Server: Windows Authentication Mode and Mixed Mode.
- Mixed Mode = MSDE will accept both SQL login account authentications (like sa, for example) and Windows integrated authentications
- Windows Authentication Mode = MSDE will only accept Windows integrated authentications, and will not allow local SQL account authentications -- sa's pretty much useless
To the best of my knowledge there is no way to tell SQL Server 2000/MSDE to accept only SQL Server authentications, and refuse Windows authentications.
Our second step in securing any MSDE box, then, is to forgo SQL Server authentications altogether if we can. You'll see how to do that in the section on MSDE installation. If you've already installed an MSDE and allowed Mixed Mode, then I'll show you later how to change it back without having to reinstall.
Keep MSDE Up To Date: Where To Get MSDE Patches
So let's suppose that you set up an MSDE 2000 system back in, say, 2001. You set a fairly long sa password and also decided to avoid Mixed Mode, so your MSDE box only accepts Windows authentications. Sounds like you've done a good job, eh? Sure... until one Saturday in January 2003... the 24th, to be exact. A bug in SQL Server 2000 and MSDE 2000 allowed some jerk to write a worm that hopped from server to server across the Internet sniffing out UDP port 1434, trying to spread so fast that it significantly slowed down the Internet and caused any infected server to generate so much traffic that it could saturate an internal network.
I'm talking, of course, about SQL Slammer. If one of your SQL servers got slammed, then the chances were good that it made your internal network just about unusable.
In April of 2002, Microsoft announced that they'd discovered a bug in SQL Server 2000/MSDE 2000, and released a patch for the bug. In January of 2003, Slammer exploited that bug. Read that again: patch released in April 2002, bug exploited eight months later in January 2003. Had we all kept track of our SQL Server 2000 patches, then Slammer would have had a negligible effect on the world. But a lot of MSDE boxes were, again, run by people who didn't even know that they were running MSDE, and many others were run by accidental DBAs who didn't always think to patch. (I was guilty of that too -- I figured that because I was using SUS I was okay, forgetting that SUS only patched Windows. Arrgh.)
The best way to find SQL Server 2000/MSDE 2000 patches is by going to http://www.microsoft.com/technet/security and then click on "Latest Security Bulletins." (It's near the top of the middle column.) That'll take you to a search tool that first asks you to select a "Product/Technology:" -- choose "Microsoft SQL Server 2000 Desktop Engine (MSDE 2000)." Then it'll want a "Service Pack:" so enter "Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) SP3a." Click Go and you'll see any relevant patches.
And if you're thinking that you needn't look for patches because the new Microsoft Update and WSUS will go get them for you automatically, think again. Tests on MSDE 2000 Release A setups showed that a trip to Microsoft Update did not go get the MS03-031 patch. I double-checked this behavior by locating the original MSDE 2000 -- that is, the MSDE that didn't require a non-null sa password, didn't include SP3 and was potentially open to an attack by SQL Slammer. I connected that system to Microsoft Update and let it do its scan, but Microsoft Update didn't offer SQL Server 2000 SP3, much less MS03-031 or even SQL Server 2000's SP4. So a trip to the Microsoft Security Bulletin Search page on a monthly basis -- preferably after the second Tuesday of the month, when the bulletins are released -- is unfortunately still a good idea.
Our third step to securing your MSDE box, then, is to check monthly for SQL Server 2000 / MSDE 2000 patches.
Don't Allow MSDE To Accept Commands Across the Network
Most servers are pretty much useless without a network, as servers are intended to be centralized for the use of many clients. And sure, most SQL servers need to be on a network to offer their services to one and all. But when we're using MSDE, it's often only intended for one application, a local application. For example, many folks find MSDE useful as a free database back-end for a Web app, so in many cases you'll see MSDE installed on the Web server itself. In that case, MSDE acts as a server that has just one client, IIS, and that client is on the same machine as MSDE is. In that case, MSDE needn't be able to accept database queries over the network.
Now, that's a real plus for securing your MSDE system, because that means that the only way to hack your MSDE system is either to physically sit down at it, or to first remotely seize control of some other application on the same box as the MSDE system. So one way to make your MSDE system more secure is to tell it not to accept requests over the network. You can configure that with a program called the "SQL Server Network Utility," and I'll cover it later in this article.
The fourth possible step to securing our MSDE box, then, is to disable network communication, if MSDE's client sits on the same physical system as MSDE.
Block SQL Server Ports
As long as I'm on the subject of securing your MSDE system, let me toss in one more brief one: ports. Any remote attacker must be able to communicate with TCP port 1433 and, to a lesser extent, UDP port 1434 on your MSDE 2000 or SQL Server 2000 box. So you can add another layer of defense on your MSDE box by making it difficult or impossible for outsiders to connect to those ports on the system.
Step five in securing your MSDE system is to block TCP port 1433 and UDP port 1434 from those who shouldn't be accessing them.
I hope I haven't scared you away from using MSDE? Good. Then let's see how to acquire and install it.
Finding MSDE and its Friends
You can download MSDE by going to www.microsoft.com/downloads and search for "msde 2000." While you're there, download these files:
- "Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A." This is MSDE 2000 with SQL's Service Pack 3 installed, and it's about 44 MB.
- You do not need SQL Server 2000 SP4, even if it is the first option offered. It wouldn't hurt to install the SP4 for MSDE 2000, but it's an 80 meg download and as far as I can see you're perfectly fine with MSDE 2000 Release A and the following patch, MS03-031.
- As I write this, you need "SQL Server 2000 (32-bit) Security Patch MS03-031." It's discussed in KB 815495. Its file name is SQL2000-KB815495-8.00.0818-ENU.exe.
- Go to http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp and download "SQL Server 2000 Books Online (Updated 2004)."
As I write this, MS03-031 is the only patch that Microsoft's site says that MSDE 2000 Release A needs, but as you know, that may change. To find any others, follow the advice in the earlier section "Keep MSDE Up To Date: Where To Get MSDE Patches." Again, this is very important. MSDE hasn't had a lot of security problems, but when the SQL Server world spawns a security bug, it's a doozy!
Installing MSDE
Start up the install EXE, agree to the license, and then tell it where to put the unpacked files -- C:\MSDERelA is the default but you may want something different -- and you'll have all of MSDE's install files in \msderela. (It'll probably ask you if it's okay to create the \msderrela folder; tell it yes.)
In the C:\msderela folder you'll find a setup.exe program, but don't run it yet. If you do, then setup.exe will think about it for a minute or two, and then terminate with the message
"A strong SA password is required for security reasons. Please SAPWD switch to supply the same. Refer to readme for more details. Setup will now exit."
As I've said, MSDE comprises most of SQL Server 2000, but not all of it. One of the things missing is the nice GUI installation program that SQL Server 2000 gets. Instead, we get a command-line program, setup.exe, that needs some options specified or it won't run. To understand what MSDE's setup.exe needs, however, I've got to get you up to speed on a few things.
MSDE 2000 Release A Requires A Non-Blank sa Password
Even though it disables SQL authentication by default, the setup.exe that comes with MSDE 2000 Release A requires a non-null password for sa. You specify that with the "sapwd=" parameter. So, for example, to set up MSDE to run on your system with an sa password of "bigsqlpassword" then you'd invoke setup.exe by first opening a command prompt, then changing the directory to c:\msdrela and then typing
setup sapwd=bigsqlpassword
Alternatively, if you actually want to specify a blank sa password for some inconceivably sick, twisted, perverse reason (or, more likely, because you're supporting some ancient program that requires it), then skip the "sapwd=" parameter and replace it with "blanksapwd=1." I don't recommend it, if you can avoid it. The sapwd or blanksapwd parameters are the only ones that the MSDE 2000 Release A setup.exe requires, but you may want some others, so please keep reading...
Understanding MSDE/SQL Server 2000 "Instances"
SQL Server 2000 and its smaller sibling MSDE 2000 allow you to install more than one "instance" of SQL Server to a given computer. It's like running more than one copy of SQL Server on a given system. Why would you do that? Probably because you need to run not only SQL Server 2000 but also older versions, like SQL Server 7.0 and SQL Server 6.5. Why do that? Well, as with every other kind of server, whether it's a file server, domain controller, e-mail server or database server, upgrades can be a pain. Suppose you run a big corporate SQL Server that hosts databases for four different departments. Department One has a database they've been running since SQL 6.5 and they've never redesigned it to use 7.0 or 2000 features, and lack the time or the interest to do that upgrade. But you want to upgrade that database server from SQL Server 6.5 to SQL Server 2000. You could buy a new machine, but you needn't. SQL Server 2000 can live side by side with other copies -- "instances" -- of SQL Server, even if they're not the same version.
Where might you want to run multiple instances of MSDE? Well, suppose you're installing WSUS on one of your company's servers. The company has a fully licensed copy of SQL Server on that machine, but you're not one of the "official" DBAs. So you go to one of the DBAs and ask if it's okay to stuff another database on the corporate SQL Server. You are met with a cold stare. You may, the DBA explains huffily, install your WSUS database on the machine... but you may not put their precious SQL Server in charge of it. In other words you may use the hard disk on the machine, but not the SQL Server service. So what do you do? You install MSDE as a "second instance" of SQL Server 2000 on the server.
You tell MSDE to install itself as an instance with the instancename= parameter, added to the setup.exe command line. So, for example, to create your MSDE for WSUS's use, you might add the parameter "instancename=wsusdb" when you type the setup.exe program at the command line. Every instance will need a new port; the first instance, or "default instance," runs on port 1433. Subsequent ports find their own port dynamically.
"Dynamically?" Yes. A client program would issue commands to the default instance of SQL Server 2000 or MSDE by connecting to the server's TCP port 1433. But if that client program wanted to connect to another instance on that database server machin, then that client program would have to first discover that instance's port on the server. It can get that information by asking the server's "SQL Server Resolution Service," which lives on every SQL Server 2000/MSDE system's UDP port 1434. So suppose I had an instance called "msdetest" that had found a home on TCP port 2111. An application trying to get to the "msdetest" instance would send a question to UDP port 1434 on that server asking, "what TCP port do I use to talk to msdetest?" And the SQL Server Resolution Service would reply, "TCP port 2111." It was a buffer overflow in the SQL Server Resolution Service that led to Slammer (that's why blocking UDP port 1434 stops Slammer), but inasmuch as MSDE 2000 Release A incorporates MS02-039, the original security bulletin that included the fix for Slammer's vulnerability, then you needn't worry about that worm.
By the way, I've tested a few systems with instances and have found that if you just want to get to the default instance, then your clients do not need access to UDP port 1434. Only if a client wants to get to an instance other than the default one does it appear that the client needs UDP 1434.
I'm not going to talk much more about instances; I just wanted to mention it for a few reasons: first, when you read SQL documentation you'll sometimes see references to instances. Second, explaining it gave me an excuse to explain why SQL servers and MSDE need UDP port 1434, and, finally, because being able to build an instance may be of help to those needing to host MSDE databases on systems that already contain a SQL Server 2000. You do not need to specify an instance to make MSDE work and in fact if you don't then your MSDE is the "default instance" on that system, which simplifies things a bit.
So, for example, to create an instance named "mytestmsde," you'd type
setup sapwd=bigsqlpassword instancename=mytestmsde
"DisableNetworkProtocols" Lets You Enable or Disable Network Access
You've already read that one way to secure your MSDE system would be to deny database services to any applications except the ones installed on the same system as the MSDE software. By default MSDE 2000 Release A's setup.exe disables network access to MSDE. You can change that, if you need to, with the setup.exe "disablenetworkprotocols" parameter. Just set it to "disablenetworkprotocols=1" to disable network access -- which is, again, the default -- or "disablenetworkprotocols=0" to enable network access. Yes, it seems a little odd, but now and then Microsoft likes to see if we're actually paying attention -- as the parameter says DISABLEnetworkprotocols, then a "1" "enables the disabling," if you follow the closely-parsed logic. Anyway, to let systems request database services from your MSDE system over the network, set the parameter to "disablenetworkprotocols=0." To disable network access, set it to 1 or just leave it out, as it's the default behavior.
So, for example, to create an MSDE instance of "mytestmsde" with an sa password of "bigsqlpassword" and enable network access, start up setup like so:
setup sapwd=bigsqlpassword instancename=mytestmsde disablenetworkprotocols=0
SecurityMode: To sa or Not To sa?
By default, MSDE 2000 Release A's setup.exe disables SQL account authentications and puts the MSDE system in Windows Authentication mode rather than Mixed Mode. You control that with the "securitymode" parameter. This is an odd one -- as far as I can see, there is one and only one possible value for this parameter -- "sql." In other words, if you don't specify a "securitymode=sql" parameter on the setup.exe line then you get Windows integrated-only authentication. Specifying "securitymode=sql" on the setup.exe line gets you Mixed Mode, where you can do either Windows integrated or local SQL account authentication (sa, that is). So if we wanted to really live out on the edge and allow network access, sa logons, create an instance named "mytestmsde" and give sa a password of "bigsqlpassword" then we'd start up setup like this:
setup sapwd=bigsqlpassword instancename=mytestmsde disablenetworkprotocols=0 securitymode=sql
That'd have to all be typed on one line, even if it did break on the printed page or on your browser.
Running setup.exe
I'd like to show you both SQL and Windows logons, so let's install MSDE with a password of "bigsqlpassword" and enable Mixed Mode authentication. There's no need for an extra instance or to enable network protocols. And if enabling Mixed Mode worries, you, then don't worry -- I'll show you how to change the authentication type (and the network protocols) after the fact. So open up a command prompt and navigate to the \msderla directory:
cd \msderla
Then start the setup with this line:
setup sapwd=bigsqlpassword securitymode=sql
Remember, the only one of those parameters that you must have is the sapwd parameter. (Or, I suppose, the blanksapwd parameter.)
But there's tons more that you can tell MSDE's setup.exe to do. To see all of its options, download, install and open up SQL Books Online and look in the Index under "Customizing Desktop Engine Setup.exe." You can alternatively put your desired options into an ASCII text file named setup.ini that you place in the same folder as setup.exe. It looks like
[Options] sapwd=bigsqlpassword disablenetworkprotocols=1 securitymode=sql
If you don't want Mixed Mode authentication, then just delete the "securitymode=sql" line. Once you've got a setup.ini, then all you need to do is either double-click on the setup.exe in Explorer, or type "setup" from the command line.
Patching Your MSDE Setup With MS03-031
As far as I can see, as least as I write this in July 2005, there is only one critical patch that you must add to a freshly-installed MSDE 2000 Release A install -- MS03-031. Applying it is simple: just download SQL2000-KB815495-8.00.0818-ENU.exe as mentioned earlier, and run it. It unpacks some files, and then starts a wizard called "Hotfix Installer." Click Next, agree to the license, Next, and pick the instance that you want to patch, which is probably the only instance, and Next. It then asks you if the hotfix installer should log onto MSDE using your currently-logged-on Windows credentials ("Windows Authentication") or using the sa account. If you choose the sa account, then you've got to tell the Hotfix Installer your password. Choose one and click Next and wait a few seconds, and when the "Install" button appears, click it. It stops and patches the SQL engine, which is pretty simple inasmuch as the SQL engine's not yet running, applies the patch, and finally tells you that it's installed. Click Finish and you're done.
Installing SP4 On MSDE
As far as I can see, you do not need to install SQL Server 2000's SP4 on MSDE 2000 Release A. But what if you'd like to be as up-to-date on MSDE/SQL Server 2000 patches as possible, then let's install SP4. (I'm also showing you how to do it because it's not nearly as easy as was installing MS03-031.)
First, download it from www.microsoft.com/downloads by searching for "sql server 2000 sp4." Clicking that link will take you to a page that offers you several downloads towards the bottom of the page in a section entitled "Files in This Download." You want the version for MSDE, SQL2000.MSDE-KB884525-SP4-x86-ENU.EXE. It's an 80 MB download, so if you decide at this point to just stick with MS03-031 then I think no one will blame you. And yes, you did read that right -- the original MSDE 2000 Release A download was 43 megs, and the SQL Server service pack specifically built for MSDE was 80 megs. Doesn't seem to make sense, does it? Reader Ed Braiter tells me that one thing about this MSDE SP4 is that you can do a fresh install right from it, so you could in theory just download this. But I'm assuming that many of you have the MSDE that came with whatever utility you're installing, and that's almost certainly MSDE 2000 Release A -- the version with SP3 incorporated.
Anyway, assuming you've downloaded the file, run it -- it's an executable -- and you'll end up with a folder named C:\SQL2KSP4\MSDE that contains the service pack. Open up a command prompt and navigate to c:\sql2ksp4\msde. Then type
setup /upgradesp sqlrun
If you'd like to keep a log of the SP4 install, add "/L*v filename" to create a log by that name. Permit me to rant for a moment? Thanks ... Note the cumbersome and non-intuitive nature of this SP install; it took some poking around to figure that syntax out. Simply typing "setup" -- which works for every Windows service pack that I've ever installed -- yields the incredibly un-helpful message "The instance name is invalid." End of rant.
But here's one neat advantage of installing an SQL Server service pack over installing an Windows service pack: you don't have to reboot!
Verifying Your MSDE Setup
Looks like we've got MSDE installed, but is it really there? We can find out with a few clues that MSDE leaves around that signal its presence.
First and, well, most obviously, MSDE adds two new services to your system. Open up the the Services snap-in or click Start/Run and type "services.msc" and press Enter, and you'll see that you now have a service called MSSQLSERVER and another called MSSQLServerADHelper. (If you'd installed an instance then you'd have another service whose name was "MSSQLSERVER$" with the instance name appended, as in "MSSQLSERVER$MSDETEST.") The first will be set to automatically start, but Setup doesn't start it. So let's fire it up -- open a command prompt and type
net start mssqlserver
Or of course if you're already in the Services snap-in then you can right-click the service and start it. You can then run a program called the "SQL Server Service Manager," a tool of dubious value but what the heck, it's one of the few that we MSDE guys get. It's in C:\Program Files\Microsoft SQL Server\80\Tools\Binn by default and the file's name is sqlmangr.exe. It puts an icon in your system tray that indicates whether MSDE's up or down with a small red dot or a tiny green triangle.
Now, if you'd enabled network protocols then you'd see another symptom of SQL Server/MSDE's presence -- two newly opened ports. On a system that's got SQL configured with network protocols enabled, open a command prompt and you'll see, among other things, two lines:
TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING
and
UDP 0.0.0.0:1434 *:*
This shows that your SQL Server software listens on TCP port 1433 and UDP port 1434. Again, if you try this with the configuration that we've installed then you will not see either of those ports in a netstat command. But if you're curious, then hang on a bit, as I'll show you how to enable network protocols.
Where Everything Is
Congratulations, you've got an MSDE server running! But where is everything? There aren't any new entries in Start Programs, no shortcuts on the desktop... what do I do now?
First, let's see where the setup program put everything. Open Explorer and navigate to C:\Program Files\Microsoft SQL Server. Inside that directory you'll see a folder named "80" and another named "MSSQL;" look in MSSQL. In there you'll see various other folders, but the one that you'll probably first care about is Data. C:\Program Files\Microsoft SQL Server\MSSQL\Data is, by default, where you'll find your databases once you start creating them. It's also where you'll see some files that support some automatically-installed databases called master, model, msdb and tempdb. If you've created an instance, then that instance name will be appended to the "MSSQL$" folder name. So, for example, if you installed MSDE as an instance named "wsusdb," then your data would live in C:\Program Files\Microsoft SQL Server\MSSQL$wsusdb\Data.
Controlling MSDE With OSQL -- the Basics
If you'd shelled out that four grand for a full-blown copy of SQL Server then you'd have some neat stuff, including that nifty GUI tool called Enterprise Manager. But you don't get that with MSDE. (If, however, you work for an organization with the right SQL Server licensing, then you can, of course, legally install Enterprise Manager and it will control MSDE just fine. But you've got to have the license to do this or you'll put yourself in trouble with the software police, and that's not a nice position to be in.)
But you really can do an awful lot of MSDE administration with a command-line tool called osql that gets installed with MSDE. To start it up, close the command prompt window that you've been working with, and open a new command prompt (you do this because installing MSDE changed the system's PATH variable -- opening a new command prompt brings along the new PATH) and type
osql -U sa -P bigsqlpassword
You're now logged onto your local MSDE server with this prompt:
1>
Not terribly friendly, granted -- it assumes that you know a language called "Transact-SQL" or TSQL. We'll learn some of that soon and I'll point you to an big on-line reference for it. It's quite an extensive language but I'll give you cookbooks for everything that you'll need to know.
To get out of osql, just type "quit" and Enter.
The osql statement that we first did logged you in as sa, which you may recall is the local SQL "god" account. You can alternatively log on using Windows Integrated authentication. When you do that, you're just saying to osql, "I think I have the permissions given my Windows logon account to do what I need to do with MSDE, so just log me on using my Windows account." Assuming that you've quit out of osql, you can do a Windows Integrated authentication by typing
osql -E
Which way should you log on? If you're going to build some database project from scratch, then I'd do all of my work under Windows Integrated and disable SQL authentication altogether. But as I said before, you may be just installing MSDE to make some app happy -- some app that you didn't build. In that case, check its installation notes. WSUS, ACT, and Ultrasound all work with Windows Integrated logons.
Note that the -E, -U and -P options must be in uppercase. We'll meet other osql options soon, and they're all case-sensitive. Some will need uppercase, some lowercase.
Creating And Deleting Databases
Now that you've got a running database server, then again my intention here is to show you how to do the kinds of things that DBAs do -- secure the databases, back them up, manage user accounts, that kind of thing. But of course database servers also let you create, delete and query databases, even though we server admin types don't worry all that much about that stuff. But let put off the administration stuff for just a while so that I can show you how to create your own database, stuff some data in it, and query it, just to give you the barest feel of what database server software like MSDE is built to do. Besides, it'll give me a chance to get you comfortable with how to operate osql.
We'll create a database named "myfirst" and place inside it a table named "folks" that contains two pieces of information on each record -- a person's name and zip code. Then we'll do a few queries on it. I'll keep it as simple and example-driven as I can, and then we'll get to the admin stuff.
Right out of the box, your MSDE system comes with a bunch of pre-built databases named master, tempdb, model, and msdb. You don't want to mess with any of them, so one of the first things that you must do is to create a database.
Start out by logging onto osql with either Windows or SQL credentials. At the 1> prompt, type
create database myfirst
and press Enter. Then, on the next line (you'll see a "2>" prompt), type "go" and press Enter. You'll see a response like
The CREATE DATABASE process is allocating 0.63 MB on disk 'myfirst'. The CREATE DATABASE process is allocating 0.49 MB on disk 'myfirst_log'. 1>
Look in the Program Files\Microsoft SQL Server\MSSQL\Data folder, and you should see two new files -- myfirst.mdf (the database) and myfirst_log.ldf (the log file). Congrats, you've created your first database!
But what if you didn't want the files there? What if you wanted to give the database file or log file a different name? And what if you wanted to restrict how large this database can grow? After all, the database that you just created could in theory grow to fill the entire hard disk, unless it hit MSDE's 2 GB maximum size first. In that case, you might want to use some of CREATE DATABASE's options.
Looks like we're going to need a better-defined database, so let's get rid of this one. In osql, type
drop database myfirst
Press Enter, and then type "go" and press Enter. You'll always need to type "go" at the last line of any T-SQL command. Simply typing the command loads it into the server; typing "go" is like pressing a big button labeled "DO IT!"
A more filled-out CREATE DATABASE command might look like
create database dbname
on
( name=logicaldbname,
filename = 'filespec',
size = initialsize,
maxsize=maximumsize,
filegrowth=howmuchtoincreasedbwhennecessary)
log on
( name=logicallogname,
filename = 'filespec',
size = initialsize,
maxsize = maximumsize,
filegrowth = howmuchtoincrease)
Wondering how I found all that stuff out? If you ever want to see the complete syntax of a T-SQL command, you can look them all up in the SQL Books Online, or you can find a complete T-SQL reference at http://msdn.microsoft.com/library/en-us/tsqlref/ts_tsqlcon_6lyk.asp. And, as always, if it's not there, and, inasmuch as Microsoft loves to rearrange their Web site, then just Google "Transact-SQL reference."
Most of these options are self-explanatory, but for the parts that aren't, dbname is the database name, like "myfirst;" logicaldbname is, as far as I can see, mostly of interest to big database projects, and we're not doing one of those -- ditto for logicallogname. The sizes are all in megabytes, and the filespecs must be, as their name suggests, full file specifications -- drive letter, directory and so on, as in "c:\databases\dbfile.mdf" or the like -- "dbfile.mdf" wouldn't work.
Here's a fully completed example for myfirst:
create database myfirst on ( name=myfirst, filename = 'c:\dbs\myfirst.mdf', size = 10, maxsize=75, filegrowth=5) log on ( name=myfirst_log, filename = 'c:\dbs\myfirstlog.ldf', size = 5, maxsize = 25, filegrowth = 5)
But for heaven's sake, don't fire up osql and type all that in, because there's another way to use osql...
More On Controlling MSDE With osql
That all looks like a lot of trouble to type, doesn't it? I mean, you could type all that stuff -- followed by "go," of course -- and you'd create the database. But that seems so dumb -- it's 13 lines of code to type in and, of course, one error and it's all for nought.
So, of course, there is an alternative.
Feeding a Pre-Built T-SQL Statement To osql
Just take those thirteen lines and type them into Notepad (or, for that matter, just copy them from this page and paste them into Notepad) and save them under any name -- just for chuckles, let's save them as c:\makefirst.sql. Note that you do not have to end the file with a "go." And inasmuch as we want to put those databases in a folder named c:\dbs that doesn't yet exist, then open up a command prompt and create that folder by typing
c: md \dbs
(My apologies to the old-timers who, like me, learned this stuff first in DOS. Remember, Windows has been around so long that there are lots of smart folks who never had to learn the command line. So no insult intended to any reader, honest.) And press Enter after each line. Now you're ready to feed that file containing the commands to osql with the -i command:
osql -E -i c:\makefirst.sql
You'll get a message to the effect that it created both the database file and the log file. Type
dir c:\dbs
And you'll see the two files.
Breaking Up Long T-SQL Statements
So we've seen two ways to feed some T-SQL commands to osql -- by typing them in and by putting them in an ASCII text file, then passing that file to osql with the -i command. There are other ways as well.
First of all, notice the sequence of lines. Did that CREATE DATABASE command have to be 13 lines? No. You can break lines pretty much anywhere there's a space for readability, or you can scrunch it up all onto one line. I could create a second database by typing
osql-E 1>create database mysecond on (name=second,filename='c:\dbs\sec.mdf',size=10, maxsize=75,filegrowth=5) log on (name=secondlog,filename='c:\dbs\seclog.mdf', size=5,maxsize=25,filegrowth=5) 2>go
While I had to break that long line into three lines to keep it on a printed page, you'd actually type it into osql as one long line. Ugly, yes, that's why T-SQL lets you break it up, and lets you store it in a file before feeding it to osql.
You Can Give osql An In-line T-SQL Statement With -Q
You can also pass any SQL command right in-line to osql with the -Q ("query") command:
osql -E -Q "text of query" or osql -U sqlaccountname -P password -Q "text of query"
Again, it can be a bit of a pain because you've got to get the syntax right, so I'll spare you our ugly CREATE DATABASE example, but you can try another one like so:
osql -E -Q "create database delme"
Note that you surround the query -- every SQL command is a "query" in SQL-talk, even if the query creates a database, deletes a table, modifies a record or whatever -- with double quotes. As you may have noticed, SQL likes to see things inside a query surrounded by single quotes, as in the filename='c:\dbs\sec.mdf' clause that we saw above.
Controlling Remote Systems With osql
What if you have more than one MSDE machine to keep track of? Well, you could, of course, run around from system to system, but the heck with that -- you can sit at any system and use osql to control any SQL Server or MSDE system in the world, so long as you can see the TCP port that the server uses. (Remember, it's probably TCP port 1433 because that's what the default instance uses. We'll see how to find the port for other instances later.)
You can install the osql.exe tool on any 2000, XP or 2003 system simply -- all you need is the osql.exe program file itself. You can find that on your MSDE or SQL Server 2000 boxes in C:\Program Files\Microsoft SQL Server\80\Tools\Binn. Then just copy the osql.exe file somewhere convenient on your workstation, like the System32 directory. Then just run osql as usual, except add the -S, for "Server name," option, followed by the server's name. To specify an instance other than the default, add a backslash and the instance's name to the server name. So, for example, if I had osql.exe on my laptop and I wanted to run some T-SQL statements on a machine at bigsql.bigfirm.com, then I'd type
osql -U sqlaccountname -P password -S bigsql.bigfirm.com
If instead I wanted to access an instance on that server called "msdetest," then I'd type
osql -U sqlaccountname -P password -S bigsql.bigfirm.com\msdetest
Note that this will not work if you've disabled network protocols on the MSDE system; do that and you can't use osql to do anything remotely. That's how to use osql; let's get back to what it can do.
How To Create A Table In An MSDE Database
Now that we've got a database named "myfirst," let's create a table in it so we can insert a few records and then do a query or two.
When you create a table inside a database you've got to specify what fields that table contains -- what are their names, what type of data do they contain, that sort of thing. As the main focus of this article isn't really table creation but more database maintenance, let me skip straight to the example. The SQL looks like this:
use myfirst create table folks ( name varchar(30) not null primary key, zip int not null )
This just says to put this new table into the "myfirst" database and call this new table "folks." The table has two fields, "name" (which is a string field that cannot exceed 30 characters) and "zip" (which is an integer). The "not null" means that MSDE should not accept any new records that lack data in those fields, and "primary key" says to sort these records by the name.
Again, you can either type this in (don't forget the "go!"), or put it in a text file and run it with the -i option.
Listing Databases and Tables in MSDE
By now you're probably wondering how to check that MSDE's actually created the database and table.
List All Databases on an MSDE Server
To see all of the databases on a server, it's just two lines (plus a "go"):
use master select name from sysdatabases
The trick to figuring that out lies in looking at the SQL Server Books Online, the T-SQL reference. There's a section there called "system tables." One of them is called sysdatabases and, well, you can figure out the rest.
List All Tables in an MSDE Database
To see the tables in a given database, we'll need to look at another system table called "sysobjects." Every database has a sysobjects and it contains a bunch of information about that database, including any stored procedures, views, triggers (these are all important SQL thingies that we can largely ignore) and ... tables! Every entry in sysobjects has a "type" value which is "U" for user tables. With this in mind, we can construct a T-SQL query:
use myfirst select name from sysobjects where type='U'
Adding Records to a Table in MSDE
Let's fill out our incredibly simple database with a few records. We do that with T-SQL statements called "insert." They look like this:
use myfirst
insert into folks values('Mark Minasi',23456)
insert into folks values('Horatio Hornblower',30101)
insert into folks values('Stephen Maturin',23464)
insert into folks values('Jack Aubrey',87981)
insert into folks values('Thomas Cochrane',51931)
insert into folks values('Richard Bolitho',10595)
Type 'em in if you like, or copy and paste and then use -i. If you type it in, don't forget the "go" at the end.
Showing Records With SQL Queries
Now that we've got a database, here are a few T-SQL queries just to offer you a "feel" for how SQL queries look.
SQL queries all use the "select" statement. The simplest SQL query is the "show me everything" query. It looks like
select * from folks
But if you just log onto osql and type that line, then Enter, then type "go" and Enter, then you'll get an error message. That's because the "folks" table lives not in the "master" database -- which is the one that you're attached to by default when you log on -- but the "myfirst" database. So we've got to first use the "use" command to tell osql where to look for "folks." That makes our query look like
use myfirst select * from folks go
That'll work, or you can do the whole thing from the C:\> prompt using the -Q (query) and -d (database) options:
C:\>osql -E -d myfirst -Q "select * from folks" name zip ------------------------------ ----------- Horatio Hornblower 30101 Jack Aubrey 87981 Mark Minasi 23456 Richard Bolitho 10595 Stephen Maturin 23464 Thomas Cochrane 51931 (6 rows affected) C:\>
Notice a few things there. First, remember that I had to log on somehow, hence the -E. I specified the database name, and the query. And remember also that I had to type -E, -d and -Q rather than -e, -d, -q because as osql is case-sensitive on its parameters.
What if we only wanted to see the people with zips between 20000 and 30000? Then we'd add the "where" clause:
C:\>osql -E -d myfirst -Q "select * from folks where zip<=30000 and zip>=20000" name zip ------------------------------ ----------- Mark Minasi 23456 Stephen Maturin 23464 (2 rows affected)
We can display just one or more of the fields by replacing the "*" with a field name or names:
C:\>osql -E -d myfirst -Q "select zip from folks" zip ----------- 30101 87981 23456 10595 23464 51931 (6 rows affected) C:\>
And any one of those names can be re-named for display purposes with the "as" clause:
C:\>osql -E -d myfirst -Q "select zip as ZipCode from folks" ZipCode ----------- 30101 87981 23456 10595 23464 51931 (6 rows affected) C:\>
By now, we've worked with osql and a bunch of its options, and played database architect/user for a while. Time to get down to administrative work.
Reconfiguring MSDE
We had to make a few fairly important decisions at Setup time for MSDE. But what if we suffer what might be called "installer's remorse" and want to change things? In just about every case, we can -- the only exception that I know of is that to my knowledge there's no way after the fact to convert a named instance into a default instance or vice versa.
Enabling or Disabling Network Protocols
We set up our MSDE system without enabling network protocols, which sounds good from a security belt-and-suspenders point of view, but limits our MSDE system a bit. Maybe I'd like to run queries on it from far away, or perhaps I'd like to be able to osql into it from afar. How, then, do I undo the effect of disablenetworkprotocols=1?
With the "SQL Server Network Utility," svrnetcn.exe. It's in C:\Program Files\Microsoft SQL Server\80\Tools\Binn by default. It's not on any menu, so just open up an Explorer window and navigate to that folder, then double-click on svrnetcn.exe. You'll see a window with two tabs, General and "Network Libraries." General is selected by default, and that's where we want to be.
In the General tab, you'll see something like the following figure:
You'll see two boxes, "Disabled protocols:" and "Enabled protocols:," and the "Enabled protocols:" box is empty. (Remember, we disabled network protocols in setup.exe.) Click "Named Pipes" and click Enable>>, then click "TCP/IP" and click "Enable>>." We've just about re-enabled the network protocols, but before we go any further, click on TCP/IP and then the Properties button. You'll see the number of the TCP/IP port that this instance of MSDE runs on. It'll always be 1433 in this case, but on a named instance -- a non-default instance -- then it'll be something else. That's the sure-fire way to find out what that port number is.
Click OK to clear the Properties dialog, then OK twice to close SQL Server Network Utility. Then restart MSDE either from the Services snap-in, or open a command prompt and type
net stop mssqlserver & net start mssqlserver
(If restarting an instance, then replace "mssqlserver" with "mssqlserver$instancename" When you see the message "The MSSQLSERVER service was started successfully," then your system's ready for networking. For example, suppose your MSDE server is called MSDETEST and your desktop PC is called DESKTOP. You'd copy the osql.exe file from MSDETEST's C:\Program Files\Microsoft SQL Server\80\Tools\Binn directory to System32 on DESKTOP. Then you'd open up a command prompt on DESKTOP and type
osql -S MSDETEST -U sa -P bigsqlpassword
To disable network protocols, just restart svrnetcn.exe and use the "<<Disable" button to yank out the "Named Pipes" and "TCP/IP" protocols, then exit SQL Server Network Utility and restart your MSDE server.
Changing Authentication Mode Between Mixed and Windows
It was convenient just a moment ago being able to use sa to log onto a remote system -- the Windows account that I was logged onto on DESKTOP probably wouldn't mean squat to the MSDETEST system, so a -E logon probably wouldn't have worked. But we really should work in Windows integrated authentication only. How to tell our SQL Server/MSDE box not to accept local SQL authentications? With a Registry hack, so open up Regedit.
If you want to modify the logon behavior of a default instance, then navigate to HKLM\Software\Microsoft\MSSqlserver\MSSqlServer or, if you want to change the logon behavior of a named instance, then navigate to HKLM\Software\Microsoft\Microsoft SQL Server\InstanceName\MSSQLServer. Then look for the Reg_DWORD "LoginMode" -- Microsoft's KB 322336 says these settings work:
- LoginMode=1 enables Windows Authentication Mode, which means only MSDE only allows Windows integrated authentications.
- LoginMode=2 enables Mixed Mode, so your MSDE allows either Windows or local SQL logins.
Oddly enough, I found that my LoginMode was already set to 0, and my MSDE system was accepting both Windows and SQL logins. In my experience, either 0 or 2 puts your system in Mixed Mode. As with the change for network protocols, restart MSDE's MSSQLSERVER service to see this change take effect.
Limiting the RAM That MSDE Uses
I once ran a SQL Server 2000 system on a member server to support a small database application. After installing the app, however, I noticed that everything else on the system had slowed to a crawl. A quick look in Task Manager showed me that SQL Server had sucked up over a gigabyte of memory, which was odd considering that the database was no more than a few tens of megabytes on disk. I soon found out that one of Enterprise Manager's great virtues was that you could use EM to tell SQL Server, "don't use more than X bytes of RAM." Lacking EM, how do we do this in MSDE? With osql and a stored procedure called sp_configure.
What's a stored procedure, you say? SQL servers can contain a library of pre-built routines, either compiled with a language like C or Visual Basic, or just written in T-SQL; those routines are called "stored procedures." They're of great use to SQL developers, but Microsoft also uses them to pre-install a bunch of useful utilities. There are over a hundred of them built into SQL Server 2000/MSDE, and they've all got names that start with 'sp_" -- so clearly "sp_configure" lets us configure something -- system parameters, in this case.
sp_configure lets you control a lot of under-the-hood things in SQL Server/MSDE. It looks like
exec sp_configure 'option name','new option value'
Once you've executed the sp_configure with a "go," then you've got to type "RECONFIGURE" and "go." That'll make the change take effect. If you need to restart your MSDE to see the changes take effect, osql will prompt you.
By default, sp_configure only lets you tweak a subset of the things that it can do. So the first thing to do -- and you've only got to do it once -- is to change an option called "show advanced option." Get into osql and type
1> exec sp_configure 'show advanced option','1' 2> go Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. 1> reconfigure 2> go 1>
Now we're ready to cap the RAM that MSDE can use. You do it with an option "max server memory" and specify the max size in megabytes. To tell our MSDE system not to use more than 10 MB, we'd type
1> exec sp_configure 'max server memory','10' 2> go DBCC execution completed. If DBCC printed error messages, contact your system administrator. Configuration option 'max server memory (MB)' changed from 30 to 10. Run the RECONFIGURE statement to install. 1> reconfigure 2> go
Remember that you'll always need to first exec sp_configure, then "go," then RECONFIGURE and "go" to see the change take effect. Now, understand that this is not an extremely firm maximum for some reason. For example, if you set the max to 4 -- four megabytes -- then my MSDE system grabs about 11 MB anyway. It seems to give itself a few megs above the limit you set and/or about 15 percent more than you set. The main value of this, then, is that it lets you cut an MSDE system's RAM hunger down from, say, 1.2 GB on a system with 1.5 GB RAM down to around 400 MB. Yes, you may end up with an MSDE that's actually using 460 MB instead of 400 MB, but that's a lot better than 1.2 GB. Perhaps "max server memory" should be renamed "suggested max server memory." Unless I missed the "ignore the administrator and do what you feel like" setting. I guess I'd set that to zero, and of course then I'd have to remember to reconfigure; if only such a setting existed...
Managing User Accounts in MSDE
As with a lot of things security-wise, SQL account management was apparently pretty easy back in the old days. People always logged on as sa, and the sa passwords were all blank anyway. But with the advent of that scary old Internet, we've got to protect our SQL servers, inasmuch as they can be a fruitful point of entry for a bad guy. So we've got to learn how to create SQL accounts other than sa, learn how to change the sa password now and then, we need to de-emphasize that sa account as much as is possible, and we've got to be able to use Windows accounts as much as possible in lieu of local SQL accounts.
Why Would You Want to Create New SQL Accounts?
Isn't sa good enough? Sure, sa can do anything that you want. But unfortunately sa might also be able to do anything that the bad guys want. So it could make sense to create some lower-power accounts. But let's get more specific.
Suppose you've installed SQL Server 2000 or MSDE not because you're going to do database design or programming, but instead because, as I've been suggesting throughout this article, you want to run some application, but that application needs a SQL Server 2000 or MSDE system to operate.
When you install a database-dependent application, it usually comes with some kind of setup procedure that asks the usual stuff -- where shall I put the files, would you like to read the README, the kind of stuff that you've seen many times before. But a database-dependent app also often asks, "how shall I connect to the database?" You usually have to tell it two things -- what account to log in as, and what password to use for that account. Now, "account" may either be a local SQL login account, or it might be a Windows local or domain account. But I've sometimes seen SQL-dependent apps only offer the option to use a local SQL login account, and that's where the trouble starts.
If you're an accidental DBA, then you may know just enough to know that there's one pre-created SQL account that we've discussed so far, sa, and the database-dependent app's installation routine may even offer sa as the default "how do I log in?" account. You just punch in the sa password and you're good to go. And let's be clear, there is nothing technologically wrong with this -- the database-dependent app needs to log on as an account with the power to do the things that the app needs done, and sa can do everything, so everything seems fine.
But actually it's not fine because of a potential security problem, something called a "SQL injection attack." The idea is that a poorly written SQL Server 2000-dependent app might let anyone type any SQL statement into the app. For example, suppose MSDE is the back-end database for an online forum like mine at www.minasi.com/forum. To join the forum, you create a user account and password, and give your e-mail address. It was possible in old versions of Snitz (but hasn't been for a couple of years) to type in not an e-mail address but instead, with the right trick, any arbitrary SQL command. Assuming that the person who installed Snitz set it up as sa, that arbitrary SQL command would be examined by MSDE, who'd say "who asked me this, does she have the power to ask me to do this command?" As Snitz was connected to MSDE as sa, then the answer would be "sure she's got that power, she's sa." Now imagine how dangerous this was -- an attacker could tell the computer running MSDE to
- Dump all the Snitz user names and passwords in the local forum's database
- Browse any other databases on the system
- Delete any databases or tables
- Run xp_cmdshell and do things like e-mail your password data somewhere, install back doors on your system, perhaps gain access to your domain controllers, etc.
Note also that nowhere did the attacker need to get the sa password to do this. Furthermore, if the MSDE software and the IIS software were running on the same system, as is often the case, then you could have disabled network protocols... but it wouldn't matter, because the database-dependent Web application provides the easiest networking interface we know nowadays -- a Web page.
In other words, the seemingly-innocent combination of (1) installing a nice, free, useful app that requires a database, (2) installing MSDE 2000 Release A with a strong sa password and disabled network protocols on your Web server, (3) telling the app to connect to the database as sa or, equivalent Windows-authenticated account and (4) an innocuous bug in that nice, free, useful app could lead to an attacker quickly and easily compromising every single system in your network. Yikes.
The answer? To create accounts with many fewer powers than sa, and when database-dependent apps ask how to connect to the database server, then use those lower-power apps. To do that, however, we accidental DBAs need to know how to create accounts and how to give them as much power as they need... but no more. So let's get started.
How To Create A SQL Account
While we'd prefer to use Windows integrated logons, they're not always possible. So let's start our look at SQL accounts and access with old-style local SQL accounts and move from there to letting Windows accounts play with MSDE. We create new SQL login accounts with a stored procedure called sp_addlogin. As its name suggests, it creates new local SQL login accounts.
Run it from osql like so:
exec sp_addlogin 'sqlloginname','password'
Type Enter, and then "go," and then Enter again. So, for example, suppose I want to create a user account of less power than sa so that I can use that user account to grant a program access to a database. We'll call that login account "lesspowers" and give it a password of "whocares" like so:
First, get into osql.
Then type
exec sp_addlogin 'lesspowers','whocares'
Press Enter, then type "go," then press Enter again. You'll see a response like
New login created.
I used single quotes around the name and password, but in fact if there are no spaces or other non-alphanumerics in the name or password then they needn't be surrounded by single quotes. Notice also that you can do this from one command line. Remember -Q? Well, in SQL terms, any command is a "query." We could create a SQL login account named "jack" with a password "jackpw" in one line like this:
osql -E -d master -Q "exec sp_addlogin jack,jackpw"
How To Change A SQL Account Password
Any SQL account's password can be changed with a stored procedure called "sp_password." You pass it three parameters: the account's current password, the desired new one, and the SQL login account name. So, for example, to change the password on a local SQL account "mark" with a current password of "ancientpassword" to a new one of "modernpassword," then log onto osql, then type
exec sp_password ancientpassword, modernpassword, mark go
You'll get a response of
Password changed.
(Sometimes osql can be kinda terse.) But what if you don't know the old password? Well, assuming that you're logged in as sa or a local administrator, then you can skip needing the current password -- just put the word "null" in its place. So if I didn't know Mark's password but wanted to change his password to "strongpassword" I'd get into osql and type
exec sp_password null,strongpassword,mark
Press Enter, type "go," press Enter again and the password's changed. And since this is a pretty common question -- "how do I change the sa password in MSDE?" -- then let's make it a one-liner. From a command prompt at the MSDE machine, log in as a local administrator and type
osql -E -Q "exec sp_password null,'new sa password',sa"
How To Delete A SQL Account
When we're done with an account, let's get rid of it! To do that, use the stored procedure "sp_droplogin." Just execute it with the SQL account to delete from inside osql, like so:
exec sp_droplogin jack
And of course don't forget "go."
How To List a SQL System's Login Accounts
While we're on the subject of eliminating obsolete accounts, how can we see all of the local SQL accounts? By querying a system table called "syslogins." The query looks like
select lname from syslogins where isntname=0
As before, either make it a one-liner with
osql -E -Q "select name from syslogins where isntname=0"
Or start osql, wait for the "1>" prompt and type the SELECT statement, then press Enter, then type "go" and press Enter.
Side Point: Tweaking osql Report Output
This is a slight digression, but it may keep you from throwing your computer out the window when you start querying things. If you did the above query, you probably got some really ugly output
But you'll hate the output -- it'll look something like
name ------------------------------------------------------------------------------- ------------------------------------------------- sa lesspowers
What's going on here? If you look in SQL Books Online for information on the "syslogins" table, then you'll see a listing of all of the pieces of information that syslogins contains. Next to the line describing "name," you'll see "nvarchar(128)." That means that the syslogins table sets aside up to 128 characters to store each name. When you SELECT, then osql isn't smart enough to recognize that most names aren't 128 characters, and so the "name" column in each line of the report is 128 characters wide... ugly. What can we do about this?
One approach is simply to surrender and tell osql, "go ahead and make believe that the screen is really wide," so that it doesn't break the lines. You can do that by going to the command prompt's Properties page (click the little "c:\" icon in up the upper left-hand corner of the command prompt window, click Properties and then the Layout tab and you'll see where you can do that) and set the "Screen Buffer Size" width to something a bit larger than 128 characters -- say, 140 characters. Then inform osql of the change in screen width by quitting out of osql (if you haven't already) and re-start osql, but add the "-w 140" parameter. So, for example, if I were entering osql with a Windows integrated logon, I'd type
osql -E -w 140
And then I'd do the SELECT.
Alternatively, you can tell SELECT that you're willing to throw caution to the wind and live with just seeing the first 30 characters of name, you can use the LEFT() function. That SELECT would look like
select left(name,30) from syslogins where isntname=0
Which, after the "go," produces a nicer output:
1> select left(name,30) from syslogins where isntname=0 2> go ------------------------------ sa lesspowers (2 rows affected) 1>
Note that when you do that, you lose the column header. Add an AS clause and you get it back, as with this query:
select left(name,30) as 'Login name' from syslogins
Which produces output like
1> select left(name,30) as 'Login name' from syslogins 2> go Login name ------------------------------ sa lesspowers (2 rows affected) 1>
But enough pretty-fying, back to some work.
The "isntname" parameter lets SQL tell the difference between local SQL logins and Windows integrated accounts. Leave the "isntname=0" part off to see all accounts, or run it with "isntname=1" to see the Windows accounts that SQL is willing to let log in.
How To Give An Account Access To A Database
Before seeing how to list accounts and make the report readable, we'd been creating new login accounts, and we'd created one called "lesspowers." Try logging on as lesspowers and see what you can do:
C:\>osql -U lesspowers -P whocares 1> use myfirst 2> go Msg 916, Level 14, State 1, Server MSDETEST, Line 1 Server user 'lesspowers' is not a valid user in database 'myfirst'. 1> exec sp_addlogin newguy,newpwd 2> go Msg 15247, Level 16, State 1, Server MSDETEST, Procedure sp_addlogin, Line 18 User does not have permission to perform this action. 1> exec sp_password null,newpssword,sa 2> go Msg 15210, Level 16, State 1, Server MSDETEST, Procedure sp_password, Line 20 Only members of the sysadmin role can use the loginame option. The password was 1> exec sp_password whocares,icare,lesspowers 2> go Msg 15210, Level 16, State 1, Server MSDETEST, Procedure sp_password, Line 20 Only members of the sysadmin role can use the loginame option. The password was 1> exec sp_password whocares,icare 2> go Password changed. 1> quit C:\>
Hmmm, old Les can't do much, can he? He logs on and is connected to the Master database immediately. He tries to connect to "myfirst" and he can't even look at the database by default. So then he tries creating a new user, no dice. Then he tries to change the sa password, and osql chuckles at him. Then he tries to change his own password and fails, until he actually reads the error message. You can't use the third parameter in an "exec sp_password" unless you're a system admin; everyone else must specify a current password and a new one, and leave the account name off, because everyone else is only allowed to change their own passwords.
Well, it's nice that by default the lesspowers login is pretty innocuous, but we've got to give it some access to get anything done with it. You grant an account access to a database with the sp_grantdbaccess procedure. It's a two-step procedure.
To grant user "lesspowers" access to database "myfirst:"
- First, log onto osql as sa or a local administrator.
- Connect to the database in question with the "use" command; here as the database is named "myfirst," you'd type "use myfirst."
- Then execute the sp_grantdbaccess procedure. It takes just one parameter, the login to grant access to the database.
Here's how something like that would look.
C:\>osql -E 1> use myfirst 2> exec sp_grantdbaccess lesspowers 3> go Granted database access to 'lesspowers'. 1> quit
While logged onto my MSDE test system as a local Windows administrator, I log in using my Windows credentials -- that, recall, is the -E option on osql. Then I connect to "myfirst," and press Enter, and then execute sp_grantdbaccess to lesspowers. A "go" makes it all happen and I get a confirmation.
Now what can lesspowers do? Well, now he can "use myfirst," but that's about it. If lesspowers logs in and connects to myfirst, and then just tries to view myfirst's one table ("folks," you may recall) with a "select * from folks" gets him this error:
SELECT permission denied on object 'folks', database 'myfirst', owner 'dbo'.
This may look a bit strange, but it's one of SQL Server's great strengths. Just as NTFS lets you get very, very specific about who can access what files in what way, SQL Server has layers of permissions. In the SQL world, we grant permissions based on particular SQL statements. We've met several SQL statements so far -- CREATE DATABASE, DROP DATABASE, CREATE TABLE, INSERT, SELECT, EXEC -- and there are tons more. SQL Server 2000, or, rather SQL Server 2000 and MSDE 2000 with SP3 or later, lets you grant or deny the ability to use a given command to a given login. This is a topic that can quickly get way beyond the scope that I want to cover here, but let's look at a few examples.
To give lesspowers the ability to do SELECT statements on the folks table in the myfirst database, we'd log onto osql as sa or a local admin and type
use myfirst grant select on folks to lesspowers go
You can grant more than one permission and you can do it to more then one person. For example, if I had a SQL login named "mark" I could type
use myfirst grant select, insert, update, delete on folks to lesspowers, mark go
Or there's a keyword "all" that grants permission to use all commands on a table:
use myfirst grant all on folks to lesspowers go
Taking Back Permissions
You can un-do the effects of a GRANT command with a REVOKE command. Replace the "to" with a "from" and the syntax is the same. For example:
revoke select on folks from lesspowers
revoke select, insert on folks from lesspowers, mark
revoke all on folks from lesspowers
Notice that you needn't be exact with your revokes. For example, if lesspowers only had SELECT permissions and you revoked ALL, then you wouldn't get an error message -- lesspowers would just be stripped of what little powers it had.
Summary: Creating And Empowering a SQL Login Account
Let's wrap this up with an all-in-one and, admittedly, simplified example. Suppose I'm installing a mythical online shopping cart application called Wondercart. It is, of course, a Web-based app, and it needs either SQL Server 2000 or MSDE to run. You install the app and it asks, "how should I connect to the SQL/MSDE server?" Time to cancel Setup.
You then get on Wondercart's site and look around in the section on geeky specifics or, if that's not helpful, you contact the vendor and ask, "what SQL statements does Wondercart need?" Let's say that the Wondercart folks respond that in general all Wondercart needs is SELECT, INSERT, DELETE and UPDATE. (It'd be a longer list on most real-world systems -- some EXECs, perhaps BACKUPs as well, but again I'm trying to keep this simple.) But, it mentions, the initial setup needs CREATE DATABASE and CREATE TABLE.
Hmmm... you'd rather not give potential attackers the ability to create databases or tables. (At least Wondercart doesn't need any of the DROP commands.) So you first set up Wondercart to log in as sa, so it can do whatever it needs in terms of creating tables and so on. Then you log onto your database server with a local administrator account to find out what databases it created. Your query is
select name from sysdatabases
You see a report like
1> select name from sysdatabases 2> go name ------------------------------- master tempdb model msdb cartdb (5 rows affected) 1>
You recall that master, tempdb, model and msdb are automatically created databases, but cartdb isn't. So it's the one that we look further into. Let's see what tables are in it.
1> use cartdb 2> select name from sysobjects where type='U' 3> go name --------------------------------------------------- sales customers (2 rows affected) 1>
Your goal at this point is to create an account that can access cartdb and can do SELECT, INSERT, DELETE and UPDATE on the sales and customer tables. Then you'll reconfigure Wondercart to use not sa but instead this new account for its database accesses. The intent is that if Wondercart's got some bug then yes, a bad guy might use SELECT to steal some data, but that's the worst-case scenario. He's not going to end up formatting your server's hard disk or stealing local Windows passwords.
Here are the step by steps that you'd use to create a local SQL login to make Wondercart happy. Let's say that we're going to create a local SQL login called 'wonder."
- First, create the "wonder" login with the "exec sp_addlogin" command.
- Then connect to the custdb database and grant "wonder" access to that database with the "exec sp_grantdbaccess" command.
- Use the GRANT command to give select, insert, update and delete permissions to "wonder."
Here's a sample run:
C:\>osql -E 1>exec sp_addlogin wonder,wonderpwd 2>go New login created. 1>use custdb 2>exec sp_grantdbaccess wonder 3>go Granted database access to 'wonder'. 1>grant select, insert, update, delete on sales to wonder 2>grant select, insert, update, delete on customers to wonder 3>go 1>quit
Now, are you actually going to have to do all this? Well, I hope not. A well-designed database-dependent app should come with a setup program that'll handle all that or, failing that, offer specific advice on how to set up that app in a secure fashion. But if you do have to do the developer's work, then you've got some notion of how to do it.
How To Enable A Windows Account For SQL and Database Access
In the last section, we saw that we'd create a local SQL login for the use of our mythical database app. But honestly we'd much rather avoid having to create SQL logins because of the extra management headaches that they offer. So if we've got to create special accounts for apps, let's see how to do it with Windows integrated accounts. It's just about the same as for local SQL logins with a small difference.
Recall that we used three steps to prepare a local SQL login:
- Create the account
- Grant it access to the database
- Grant it access to particular SQL statements on particular tables
With Windows integrated accounts, the "create the account" part is two steps:
- Create the domain or local account on Active Directory/Windows Local Users and Groups
- Grant it login access to the database server
- Grant it access to the database, as with a local SQL login
- Grant it access to particular SQL statements on particular tables, as with a local SQL login
First, create the Windows user. It can either be a local account, or a domain account -- either way, it doesn't matter. Suppose for this example I create a local user account on my test system, MSDETEST, called "wonderacct" with password "pwd" (sorry, my creativity is fading).
Now we've got to grant wonderacct the ability to log onto the MSDETEST database server. No, I don't mean the ability to log into Windows, wonderacct can already do that by sitting down at MSDETEST, pressing Ctrl-Alt-Del and all that. No, I mean the ability to sit down at MSDETEST, log in and then run osql -E to log onto the database engine. To do that, we'll log onto osql as sa with "osql -U sa -P bigsqlpassword" as before. Then we'd type
exec sp_grantlogin 'msdetest\wonderacct'
Note that you can either surround the Windows domain\account name with single quotes, or square brackets -- this would work as well:
exec sp_grantlogin [msdetest\wonderacct]
In that case, you don't need quotes. And I can't explain it, but once in a while the 'msdetest\wonderacct' format gets me a syntax error where the [msdetest\wonderacct] format doesn't, so it's worth trying both ways if something's not working that ought to be.
Now that msdetest\wonderacct is approved for logins, then the account is in the same position as a newly-created local SQL login, and so it needs the next two steps: access to the database and access to particular SQL statements. It looks just as it did for the local SQL login. Logging in as a local Windows administrator to grant these powers, I get
C:\>osql -E 1> use cartdb 2> exec sp_grantdbaccess 'msdetest\wonderacct' 3> go Granted database access to 'msdetest\wonderacct'. 1> grant select, insert, update, delete on sales to 'msdetest\wonderacct' 2> go Msg 170, Level 15, State 1, Server MSDETEST, Line 1 Line 1: Incorrect syntax near 'msdetest\wonderacct'. 1> grant select, insert, update, delete on sales to [msdetest\wonderacct] 2> go 1> grant select, insert, update, delete on customers to [msdetest\wonderacct] 2> go 1>
First I use the database that I want to grant access to -- cartdb in this case -- and the sp_grantdbaccess command works pretty much the same as it did for the local SQL login. Then I try to grant statement permissions to msdetest\wonderacct and you see what I mean about the single quotes versus the brackets. In just about every other case, 'msdetest\wonderacct' and [msdetest\wonderacct] work identically... but not on an MSDE 2000 SP4 machine. Hey, at least there's a simple workaround.
Armed with this information, you should now be able to create either local SQL logins or Windows accounts and grant them the (limited) access that they need to get the job done. And one more thought on this topic -- are you wondering whether SQL's got groups or not? It does, but it calls them "roles" and it's a longer story, so forgive me, but I've skipped it here.
Managing Databases
As with any other repository of important data, we soon have to ask how to protect and transport that data. As it turns out, it's fairly easy in MSDE.
How To Back Up And Restore A Database
To back up a database, you need to issue SQL statements to back up both the main database file and the log file. The BACKUP DATABASE statement handles both files:
To back up the database and its log: backup database databasename to disk='filespec-to-backup-to'
To restore it -- even if it's been completely DROP-ed -- type: restore database databasename from disk = 'filespec'
For example, let's back up myfirst. Then we'll live on the edge and drop it altogether, deleting its files. Then we'll restore it, and do a quick SELECT to see if it's truly back. Logged in as a local Windows admin, I type
C:\>osql -E 1> backup database myfirst to disk = 'c:\myfirst.bak' 2> go Processed 80 pages for database 'myfirst', file 'myfirst' on file 1. Processed 1 pages for database 'myfirst', file 'myfirst_log' on file 1. BACKUP DATABASE successfully processed 81 pages in 0.615 seconds (1.067 MB/sec). 1> drop database myfirst 2> go Deleting database file 'c:\dbs\myfirstlog.ldf'. Deleting database file 'c:\dbs\myfirst.mdf'. 1> restore database myfirst from disk = 'c:\myfirst.bak' 2> go Processed 80 pages for database 'myfirst', file 'myfirst' on file 1. Processed 1 pages for database 'myfirst', file 'myfirst_log' on file 1. RESTORE DATABASE successfully processed 81 pages in 0.675 seconds (0.972 MB/sec). 1> use myfirst 2> select * from folks where zip<=30000 and zip>=20000 3> go name zip ------------------------------ ----------- Mark Minasi 23456 Stephen Maturin 23464 (2 rows affected) 1>
Simple as that. We could easily make the backup a one-liner:
osql -E -Q "backup database myfirst to disk = 'c:\myfirst.bak'"
And from there schedule that as an "at.exe" scheduled command. Or, even better, SQL has an internal job scheduler of its own, but it's a bit beyond the scope of this article.
Moving Databases: How To Remove and Install A Database
What about when you want to move an MSDE database from one system to another, as when you decommission one WSUS server and want to move its database to another? Probably the best way is to use the SQL stored procedures "sp_detach_db" and "sp_attach_db." They look like
exec sp_detach_db databasename
and
exec sp_attach_db databasename, 'database filespec', 'log file filespec'
Here's a sample run where I detached myfirst, tried to use myfirst and (logically) got an error, and then re-attached it:
C:\>osql -E 1> exec sp_detach_db myfirst 2> go 1> use myfirst 2> go Msg 911, Level 16, State 1, Server MSDETEST, Line 1 Could not locate entry in sysdatabases for database 'myfirst'. No entry found with that name. Make sure that the name is entered correctly. 1> exec sp_attach_db myfirst, 'c:\dbs\myfirst.mdf', 'c:\dbs\myfirstlog.ldf' 2> go 1> use myfirst 2> go 1>
Ah, but what about a real test? I detached the files myfirst.mdf and myfirstlog.ldf and copied them off the computer, then wiped and rebuilt the MSDETEST system from scratch (I just love virtual machines), copied the files to c:\dbs and attached them:
C:\>osql -E 1> exec sp_attach_db myfirst,'c:\dbs\myfirst.mdf','c:\dbs\myfirstlog.ldf' 2> go 1> use myfirst 2> select * from folks where zip<=30000 and zip>=20000 3> go name zip ------------------------------ ----------- Mark Minasi 23456 Stephen Maturin 23464 (2 rows affected) 1>
Success! Moving a database from one MSDE system to another is as simple as that.
One More MSDE Hardening Tip
Before I close this section, let me pass along one a neat SQL Server/MSDE hardening tip. I've already talked about how there have been over the years several ways that an attacker could get control of your MSDE box, and that the most fruitful path for the attacker to take from that point would be to use the stored procedure xp_cmdshell to do just about anything on your system. So suppose you've disabled local SQL logins, disabled network protocols, installed a strong sa password (yes, I know I said that I'd disabled SQL logins, but what if a bad guy could flip that LoginMode Registry setting? -- consider strong sa passwords to be a belt-and-suspenders thing), I'm up to date on patches and I've tested the database-dependent apps that I run for SQL injection attacks. But what if despite all that a bad guy gets to my SQL server? What more could I do to secure it?
How about disabling the whole idea of xp_cmdshell? I ran across this in Steve and Jesper's book. There are two stored procedures in SQL Server 2000/MSDE -- sp_addextendedproc and sp_dropextendedproc. You could execute this SQL statement:
exec sp_dropextendedproc xp_cmdshell
and xp_cmdshell don't live here no more. Will you miss it? Possibly -- test it before rolling this out to production -- but if you need to restore xp_cmdshell functionality then just execute this SQL statement:
exec sp_addextendedproc xp_cmdshell
Summary: MSDE/T-SQL Cheat Sheet
Well, by my count, this newsletter works out to almost 40 pages, more if you like large fonts. But I wanted this to also be a reference, so let me finish this with a quick reiteration of the examples we've seen so far so you can just copy this last piece as a cheat sheet.
How To Run An SQL Statement in osql
osql.exe is the command-line tool for issuing SQL statements to MSDE or SQL Server. Use either the -E option to log in using your currently logged-on Windows credentials or the -U username -P password to log on with a local SQL login account. Examples:
C:\>osql -E 1>
C:\>osql -U sa -P bigsqlpassword 1>
| How To... | Command | Example (do not type the number and ">" sign; that is the osql prompt) |
| create SQL login account | exec sp_addlogin | 1>exec sp_addlogin sue,suepwd 2>go |
| Delete SQL login account | exec sp_droplogin | |
| change password on SQL account | exec sp_password | 1>exec sp_password suepwd,newsuepwd,sue 2>go |
| List all SQL login accounts | 1>use master 2>select left(loginname,30) 3>from syslogins 4>go |
|
| Grant a Windows account the right to log onto the MSDE server | exec sp_grantlogin | 1>exec sp_grantlogin [acme\joe] 2>go |
| Shift focus to a particular database on the server | use | 1>use salesdb 2>go |
| Grant a SQL login or Windows account the right to access a database (this only "introduces" an account to a database -- account can now USE database, but can't query the db) | exec sp_grantdbaccess | 1>use salesdb 2>exec sp_grantdbaccess sue 3>exec sp_grantdbaccess [acme\joe] 4>go |
| Revoke an account's right to access a database | exec sp_revokedbaccess | 1>use salesdb 2>exec sp_revokedbaccess sue 3>go |
| Grant a user the right to use a particular SQL statement on a given table | grant | 1>use salesdb 2>grant all on customers to sue 3>grant select, insert, update on customers to [acme\joe] 4>go |
| Take away a right to use a particular SQL statement | revoke | 1>use salesdb 2>revoke select on customers to sue 3>revoke all on customers to [acme\joe] 4>go |
| Create a database | create database | 1>create database salesdb 2>go |
| Delete a database | drop database | 1>drop database salesdb 2>go |
| Create a table in a database | use the database, then "create table" | 1>use salesdb 2>create table customer 3>(custid char(7) not null primary key, 4>name varchar(40)) 5>go |
| Delete a table in a database | use the database, then "drop table" | 1>use salesdb 2>drop table customer 3>go |
| Create a new record in a table | insert | 1>use salesdb 2>insert into customer 3> values('1112233', 4>'Phyllis Scott') 5>go |
| Display records | select | 1>use salesdb 2>select custid 3>from customer 4>where name='Phyllis Scott' 5>go |
| Show all records in a table | select | 1>use salesdb 2>select * 3>from customer 4>go |
| Show the first 10 records in a table | select top 10... | 1>use salesdb 2>select top 10 * 3>from customer 4>go |
| List all databases on a system | select from sysdatabases | 1>use master 2>select name 3>from sysdatabases 4>go |
| Control column width in a SELECT statement | left() function | 1>use master 2>select left(name,9) as name 3>from sysdatabases 4>go |
| List all tables in a database | select from sysobjects | 1>use salesdb 2>select name 3>from sysobjects 4>where type='U' 5>go |
| List all columns in a table | select from sysobjects | 1>use salesdb 2>select name 3>from sysobjects 4>where type='U' 5>go |
| List all tables in a database | select * from information_schema.columns where table_name='name-of-table' | |
| Back up a database | backup | 1>backup database salesdb 2>to disk = 3>'c:\backups\sales.bak' 4>go |
| Restore a database | restore | 1>restore database salesdb 2>from disk = 3>'c:\backups\sales.bak' 4>go |
| Detach a database from a server so it can be removed or moved | exec sp_detach_sp | 1>use master 2>exec sp_detach_db salesdb 3>go |
| Attach a database, perhaps to "introduce" a set of database files just moved to a server | exec sp_attach_sp | 1>exec sp_attach_db, 2>'c:\dbs\sales.mdf', 3>'c:\dbs\saleslog.ldf' 4>go |
| Enable network protocols | svrnetcn.exe | Use "Enable>>" to enable "Named Pipes" and "TCP/IP," restartMSDE |
| Disable network protocols | svrnetcn.exe | Use "<<Disable" to remove all network protocols, restart MSDE |
| Enable SQL logins | Regedit | change loginmode to 2 (see "loginmode" earlier in document) |
| Disable SQL logins | Regedit | change loginmode to 1 (see "loginmode" earlier in document) |
| Exit osql | quit | 1>quit |
Describing all that MSDE can do would take up a book or two -- that's why all of those SQL books are so thick -- so all I've tried to do here is to scratch the surface, to give you a bare minimum of information needed to install MSDE on a system so that it works, is reasonably secured, and whose files can be managed. I hope this has been useful and, as always, thanks for reading!
Installing Web Data Administrator, the Poor Man's Enterprise Manager
If MSDE interests you but command lines scare you, then you may want to look into a beta tool from Microsoft called the "Microsoft SQL Web Data Administrator" or "WDA." It's a Web-based database administration tool modeled on Enterprise Manager, and although it's not as powerful as EM, it can do a lot of things -- far more than we covered on the command line in this issue.
WDA is, however, theoretically a work in progress, although we've not seen an update since April of 2004, and its setup routine is staggeringly badly written. But if you're willing to follow a mildly complex installation procedure then you can use WDA and you just might like it. So here goes.
Ingredients:
- A Windows Server 2003 system. This might work for XP or 2000 but you'll probably have to add ASP.NET 1.1 to your IIS installation to make this work. But honestly, I only made this work on a 2003 SP1 server system, so I can't help with any problems on making other installs work. But the place to go for help on WDA questions is www.sqljunkies.com.
- Either MSDE or SQL Server 2000 installed on the system. You'll need SQL login installed, as I've not been able o to figure out how to make WDA log on with Windows Integrated accounts. (Hey, it's free.)
- IIS 6.0 already installed. (If not, look at Add/Remove Windows Components. Or see the IIS chapter in Mastering Windows Server 2003.)
Follow these exact steps -- I checked them three times -- and you'll get WDA installed. Veer from the path, and you're on your own!
Step One: Enable ASP.NET 1.1
Starting from a Server 2003 SP1 system with IIS 6.0 and MSDE installed with SQL logins enabled, you must first enable ASP.NET 1.1. I read in the sqljunkies.net forum that there's an SP1 for ASP.NET 1.1, but installing it breaks WDA. (Microsoft really needs to come up with a newer version of WDA.) So while you should have SP1 for Server 2003, avoid it for ASP.NET 1.1.
- You install ASP.NET 1.1 from "Add orRemove Programs."
- Click "Add/Remove Windows Components."
- Click -- do not check -- "Application Server," and then the "Details..." button to raise the "Application Server" dialog.
- Check the box next to "ASP.NET." (If it's already checked then just press Cancel until you've exited Add or Remove Programs and exit Control Panel and go to the next step.)
- Click OK to close the "Application Server" window.
- Click Next to tell Control Panel to install the new component.
- Once it's done, click Finish to close the applet.
- Close Control Panel.
Step Two: Download Web Data Administrator
Next, let's go get the WDA code. Released in April of 2004, it's a download of about 3.5 MB in size.
- Go to www.microsoft.com/downloads.
- In the keyword search field, type "sql web data administrator" without the quotes, and you'll get two hits. Take the newer one, called "Microsoft SQL Web Data Administrator."
- Download that file; it's called setup.msi.
Step Three: Install WDA
Double-click WDA to start its setup wizard.
- Click Next to get things going.
- Agree to the EULA, and click Next.
- Choose to either install it for just your use, or for everyone's, and click Next.
- Click Next again, and when it's done
Step Four: Start Up WDA
WDA has a little program that takes what setup.msi did and actually creates the Web site "webadmin." That's the next step.
- Run Start / All Programs / Microsoft SQL Web Data Administration / SQL Web Data Administrator.
- A small window will pop up.
- Ensure that the "IIS" radio button is clicked.
- Click the button labeled "Start."
- That will run the hard disk for a minute or two, and then automatically start an instance of IE that tries to run WDA. It will fail with a "Server Error in '/webadmin' Application" message, but don't worry about it. Close IE.
Step Five: Rearrange the webadmin Site
In theory, the WDA setup created a Web site on your IIS box called "webadmin" and, also in theory, to access that site all you need do is to fire up IE and give it an URL of http://localhost/webadmin. But the setup programs' broken, so we've got to fix it.
- Start IIS Manager -- Start / Administrative Tools / Internet Information Services (IIS) Manager
- Open the Web server's icon, the plus sign under "Internet Information Services"
- Open "Web Sites"
- Open "Default Web Site"
- Right-click "webadmin"
- Choose "Properties"
- In the "local path" field, type in or browse to "C:\Program Files\Microsoft SQL Server Tools\Microsoft SQL Web Data Administrator\Samples\SqlWebAdmin"
- Back in "webadmin Properties," click OK
- Close IIS Manager
Believe it or not, you're doing this because Microsoft fixed bugs in the code, but then left the fixed code in the "Samples" directory rather than the "this is the released code" directory. Sigh.
Step Six: Fix the Security
There is a bug in a program file that keeps everyone from using WDA. We have to fix that.
- Use Notepad to open C:\Program Files\Microsoft SQL Server Tools\Microsoft SQL Web Data Administrator\Samples\SqlWebAdmin\web.config
- Search for this string: <deny users="?" />
- Change the ? to a ! so it looks like this: <deny users="!" />
- Save the modified file, close Notepad
Now it should work!
Step Seven: Try It Out
Start up Internet Explorer and navigate to http://localhost/webadmin. You should get a simple login screen where you point to a particular database server, choose your login type and punch in name and password. lick the "SQL Login" radio button, specify a local SQL login account and click the "Login" screen. Yes, it claims to use Windows Integrated but I've not been able to make it work -- I've only gotten SQL logins to work.
From here on in, it's all GUI. I hope it's useful!
Conferences
Join me at ...
TechTarget's Web Class on SP2 and SP1. As they say it...
"Windows School is in session with Mark Minasi Mark Minasi dissects
Windows XP SP2 and Windows Server 2003 SP1 in five, 15-minute webcast
cram sessions. You can even download a worksheet to follow along with
the lesson you're hearing. Find out the good and bad about XP SP2 and
Windows 2003 SP1 in Mark's inimitable style. Topics cover: Data
execution protection, stack changes, de-anonymizing XP, IE and more."
http://searchwin2000.techtarget.com/general/0,295582,sid1_gci1084934,00.html?offer=minasi
TechMentor Fall 2005 (San Jose): 101 Communication's
semi-annual geekfest returns to San Jose this October 17-21. Follow
www.techmentorevents.com for more
info as it appears.
Windows Connections Fall 2005 (San Diego): I just got back from my magazine's twice-per-year tech-o-rama in mid-April, and it was one of the best ever, featuring great speakers and an amazing panoply of topics. I'm not sure how California managed to snag three of the four best IT shows this year (Connections in San Francisco, TechMentor in San Jose, Connections in San Diego) but if you're a fan of the Golden State's weather then 2005's the conference-going year for you! Information on Connections at www.winconnections.com. Our program chair Amy Eisenberg's trying to out-do herself so it'll be a great show.
Bring Mark to your site to teach
I'm keeping busy doing Active Directory and Security seminars and writing, but I've still got time to visit your firm. In just two days, I'll make your current NT techies into 2000, XP, Active Directory and 2003 experts. (And better yet they won't have to sit through any Redmondian propaganda.) To join the large educational, pharmaceutical, agricultural, aerospace, utility, banking, government, telecommunication, law enforcement, publishing, transportation, and other organizations that I've assisted, either take a peek at the course outlines at www.minasi.com/presentations.htm, mail our assistant Jean Snead at Assistant@Minasi.com, or call her at (757) 426-1431 (only between noon-5 Eastern time, weekdays, please).
Until Next Month...
Have a quiet and safe month.
Please share this newsletter; I'd like very much to expand this periodical into a useful source of NT/2000/2003/XP information. Please forward it to any associates who might find it helpful, and accept my thanks. We are now at over 40,000 subscribers and I hope to use this to get information to every single Mastering 2003, XP, NT and 2000 Server reader. Thanks for letting me visit with you, and take care. Many, many thanks to the readers who have mailed me to offer suggestions, errata, and those kind reviews. As always, I'm at http://www.minasi.com/gethelp and please join us at the Forum with technical questions at www.minasi.com/forum.
To subscribe, visit http://www.minasi.com/nwsreg.htm. To change e-mail, format, etc., link to http://www.minasi.com/edit-newsletter-record.htm. To unsubscribe, link to http://www.minasi.com/unsubs.htm. Visit the Archives at http://www.minasi.com/archive.htm. Please do NOT reply to this mail; for comments, please link to http://www.minasi.com/gethelp.
All contents copyright 2005 Mark Minasi. You are encouraged to quote this material, SO LONG as you include this entire document; thanks.