SQL Server Management Studio Reports — Potential Management and Permission Issues

By now, you probably know that in SQL Server 2005, a new tool that replaces SQL Server Enterprise Manager and SQL Server Query Analyzer is SQL Server Management Studio (SSMS). It has a lot of good stuff in it, one of them being the reports.

SSMS reports provide a snapshot of your server status and database status, like cpu, memory, io, cursor usage, schema changes, index usage, user statistics, etc. All these are great information available at your fingertips. MVP and PASS director Randy Dyess has a great intro post on them recently.

When I saw the demos at PASS of those reports, I was really impressed and excited about them. They definitely provide the DBAs with tons of information to proactively monitor and manage db server and databases. One thing bugged me a little during the sessions is execution permission on those reports. I could be wrong, but I believe most of the data for those reports come from DMVs or SQL Server 2005’s internal resource database. So, if you make SSMS available to developers or some regular database users, the reports will be available to them, but they cannot use them, unless they have system privileges.

To prove my theory, I created a login for AdventureWorks, a replacement for Northwind and Pubs. I gave this login db_reader and db_writer rights, a common practice for a regular user. I then logged into this database with this login, using SSMS. Sure enough, under this regular user’s login, when I clicked on any server reports, I got a permission error. I haven’t checked database status reports yet, because I’ve been playing with silent / command line install and just removed all my installations. I will test that when I get it installed again.

I think this opens a can of worms for your average DBA. Since SSMS is the only client tool available for SQL Server 2005, you don’t have a choice of whether to install this for your developers and average users. (We will have to see if Query Analyzer works with SQL Server 2005, if yes, install that for them, but that is really lame). It’s like showing a candy jar full of goodies to your child but won’t allow him to open it. The average user will constantly bug you on this and make you feel denfensive and waste your valuable time while doing it.

I then thought, geez, maybe the available reports are stored in the registry or a configuration file. If that is the case, I can then delete them after SSMS is installed on a client machine. So I did a string search in the registry, nothing came up. I then did another string search using MSN tool bar on the hard drive. Nothing came up either.

I am all for transparency and information sharing, but it seems to me that if you don’t want people to use certain feature, don’t let them know they exist in the first place. It will be great if the available reports are configurable.

What do you think?

Silent install / Command line install of SQL Server 2005 Part 3

I blogged about my 2 failed attempts of silent / command line install of SQL Server 2005 September CTP. So I filed a bug here on MSDN. I am impressed that Microsoft responded very quickly, saying this is not a bug. Thanks to whoever closed the bug. That was a real quick response:) I will redo my test asap to find out.

The reason that it is not a bug, according to the report, is that, Quote:

The message you’re seeing in the Summary.txt file is not why setup is failing. Your missing parameters for the services. From the template.ini:
;——————————————————————–
; The services for SQL Server and Analysis Server are set auto start. To use the *ACCOUNT settings
; make sure to specify the DOMAIN, e.g. SQLACCOUNT=DOMAINNAME\ACCOUNT
; NOTE: When installing SQL_Engine 3 accounts are REQUIRED: SQLACCOUNT, AGTACCOUNT and SQLBROWSERACCOUNT.

; SQLACCOUNT Examples:
; SQLACCOUNT=<domain\user>
; SQLACCOUNT=”NT AUTHORITY\SYSTEM”
; SQLACCOUNT=”NT AUTHORITY\NETWORK SERVICE”
; SQLACCOUNT=”NT AUTHORITY\LOCAL SERVICE”

; Note: To install localized versions of SQL Server, refer to the Localized Service Names table in the SQL Server Books Online topic “Setting Up Windows Service Accounts”

; Note that if SQLBrowser is already installed, SQLBROWSERACCOUNT and SQLBROWSERPASSWORD are ignored.

SQLBROWSERACCOUNT=
SQLBROWSERPASSWORD=

SQLACCOUNT=
SQLPASSWORD=

AGTACCOUNT=
AGTPASSWORD=

End Quote

Now I have 3 suggestions for the Installation help file, located at D:\SQL Server x86\Servers\Setup\help\1033\setupsql9.chm:

1. In the section titled How to: Install SQL Server 2005 from the Command Prompt, it has this to say about accounts:

[SQLACCOUNT], [SQLPASSWORD], [AGTACCOUNT], [AGTPASSWORD], [ASACCOUNT], [ASPASSWORD], [RSACCOUNT], and [RSPASSWORD]
This is information for the Service Accounts dialog box. If these parameters are not specified, Setup defaults to the LocalSystem account.

This is where it thrown me off. I intended to use LocalSystem account for startup, that is why I didn’t specify them in my config .ini file in the first place. This part of the documentation needs to be corrected, if the person who closed the bug is correct;

2. In the first post, I suggested that it would be helpful to have a template.ini file as a starting point. It turned out the setup DVD does have them, but it is nowhere mentioned in the section help file mentioned above. One would think that would be a logical place to mention them. By the way, I did visually check if I could found them on the DVD, however, I checked the wrong folder (D:\SQL Server x86\Servers\Setup). There are actually 2 copies of template.ini and they appear identical. They are located at D:\SQL Server x86\Servers and D:\SQL Server x86\Tools, respectively.

3. In the same section of the file, one command line example given was:

Setup.EXE /settings C:/set.ini /qn

This is incorrect. It should be a back slash \ after C: Unless, of course, this is Microsoft’s first step of porting SQL Server to *nix;)

All right, I will start over again. I will report back my results in a separate post;)

Vista, SQL Server 2005, AJAX, and Web 2.0

Ken’s question made me think about Vista’s impact. Virtually all Microsoft’s products depend on the win32 API, so I wonder how much of change can Vista make? In other words, if Vista breaks win32 API calls, the cascading effects it has on other products like SQL Server, Exchange, BizTalk, Office, etc., would be tremendous. It may not come down to rewrite everything, but suffice it to say that the changes and additional development work and QA regression testing effort will be significant. So is it safe to say that Vista would be an evolutionary process of Windows, not a revoluntionary change that some people are led to believe? It will be interesting to see what unfolds, as far as Vista’s impact on server applications is concerned.

To extend the thinking a little further, once the foundation is set, the rest of stuff has to build on top of it and you are limited in ways to innovate and extend your stuff. Minor modification on the framework can be done but it is going to be costly. I guess that kind of explains why AJAX and Web 2.0 is all the rage recently. The idea is that you will do most of stuff through a web interface, no custom installation and setup is needed on your workstation. Google is a pioneer in this arena. The famous AJAX/Web 2.0 products are Gmail, Google Maps. Yahoo is not too far behind. Flickr and Yahoo Mail beta are good examples.

It will be interesting to see what comes out of the recent alliance between Sun and Google. If Google can deliver OpenOffice, a competitor of Microsoft Office Suite, through a web interface, much like how it does with GMail and Google Maps, that will give Microsoft Office Suite a run for its money.

A couple of years ago, Sun’s CEO Scott McNealy mentioned that “Network is the computer”; all we need is a dumb terminal to access it. I don’t necessarily think the terminal will be dumb, because now we do need it for local storage, text editing, some file processing, especially media files like music, image, and video. But hey, I cannot pretict the future. Maybe the dumb terminal 10 years from now is 10 times smarter than the ones we have today.

Mutt questions

Originally posted at SqlServerCentral.com’s blog site:

I am working on my own website, http://www.HaidongJi.com. It is Apache on Linux, ran by a hosting company. I got access to SSH, MySql, and most of Linux utilities. I am planning to use WordPress and host a blog there too. That will be my primary blogging site, including my thoughts on Linux, MySql, personal musings, and whatnot (cannot believe I used the word). It is still a work in progress. Hopefully I can get it up and running within a month.

Anyway, the real reason I post this is to seek advice / pointers on Mutt. I am planning to install Mutt on this Linux machine and use it as my primary email client. I want to use it primarily because it allows me to use my favorite editor, VI / VIM. I also heard from people that it is keyboard driven, which I like too. The fact it is light weight also helps.

Here are my questions:
1. Installation: anything advice, how easy or difficult it is. It looks like that something special needs to be done if you are going to use SSL. Since I don’t have root access to the Linux box, will I encounter problems when install Mutt?
2. Configuration: what do I need to do to configure it so I can have access to multiple POP or IMAP accounts? It looks like I need to play with .muttrc file.

That’s it for now. Email or comment below if you have ideas/suggestions. Thanks a lot.

Silent install / Command line install of SQL Server 2005 Part 2

Today I started my second attempt of silent / command line installation of SQL Server 2005 September CTP.

I guess I should have mentioned in Part 1 that that install was attempted on Win XP Pro, SP1. This time I worked on a clean machine with a fresh Windows 2003 Standard Edition. This machine has 2 Intel Pentium 4 2.59 GHz processors and 1 GB RAM.

So, as in described in Part 1, I went to command line and fired the command.

2 seconds later, I got a message saying that this version of OS or Service Pack is not supported. All right, I pulled out my Windows 2003 Service Pack 1 DVD and installed that. I then started command line install again. So remember, SP 1 is needed on Windows 2003 prior to install.

It appeared to work initially. It looked like that prerequisite components were part of Service Pack 1, because this time it didn’t not ask for it. Therefore no mindless clicking was needed. The CD-Rom was spinning. Visual check of task manager showed there was a setup.exe process going. Would it succeed this time?

Not so fast:(. About 1 or 2 minutes later, CD-Rom stopped spinning and hard drive indicator stopped blinking. Could it finish so fast? I looked at Start->Program Files and nothing was there. I then looked at installation log file located at %ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt. Below is the message I got:

KJLLJFD-3J6YDPO : Microsoft Internet Information Services (IIS) is either not installed or is disabled. IIS is required by some SQL Server features.  Without IIS, some SQL Server features will not be available for installation. To install all SQL Server features, install IIS from Add or Remove Programs in Control Panel or enable the IIS service through the Control Panel if it is already installed, and then run SQL Server Setup again. For a list of features that depend on IIS, see Features Supported by Editions of SQL Server in Books Online.

This is really annoying. Based on BOL, none of the components I selected in my .ini file requires IIS. Yet silent install stopped because IIS was not there. In GUI install, this is presented as a warning, but you can choose to ignore the warning and click Next and continue installation. In fact, that was what I had to do today to load it on this machine.

Oh well, this is disappointing. I will see how to file this as a bug to Microsoft, if it has not been done already.