The other morning I was migrating an application from a desktop machine to server. Some real fun with this project includes zero documentation from the developer (he was a contractor who did a rush job and left). Thankfully I have experience migrating applications from dev to acceptance to prod so I knew to look for missing assemblies and whatnot.
My big headache this morning, and the source of this posting, was the missing Crystal Reports assemblies since this was developed in Visual Studio 2008. My first indication of a problem was that locally (on the server) I tried pulling up the page and got greeted with the following:
Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.
Parser Error Message: Could not load file or assembly ‘CrystalDecisions.CrystalReports.Engine, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304’ or one of its dependencies. The system cannot find the file specified.
Source Error:
After some quick Google searching I came across with my solution. They provided me links to download the runtime I needed to install. For the sake of helping others I have decided to host the files as well just in case those links die from that link. After installing the runtime on the server I restarted IIS by issuing the iisreset /restart command from the command prompt. After the restart I was able to successfully pull up my page without a problem.
This quick write up comes courtesy of a tweet by () yesterday morning (Please Note: OH means overheard, meaning that statement is something Jonathan overheard. He鈥檚 WAYYYYY too smart to actually spread something that dumb as valid advice) . Here鈥檚 the tweet:
Ok, so I鈥檝e seen a couple of stupid things written up in the last few days but this one just might take the cake. If you have been a DBA for any amount of time then you鈥檝e more than likely come across a vendor application that uses the 鈥榮a鈥� account for access to the database. I won鈥檛 get into details about the sa (or system聽administrator) account here but check out at about it ( as well to learn more).
So what exactly is wrong with that statement in the tweet? Well, as stated by () this morning:
“Ok Jorge, stop making fun of me”. No, as long as you access things using sa for “simplicity” or “optimization” I’m going to beat this over your head like an Acme mallet. Using ‘sa’ account for everything is akin to being handed the keys to the bank and being told “yeah, go ahead and make your deposits and withdrawals from your own account but try not to touch anything else while you’re digging around the vault”. Seriously, I’m not kidding. Handing someone the ‘sa’ account is handing them the keys to your SQL kingdom. Think about it, if you write an application that is accessing your database with FULL admin rights, what if someone performs a attack and drops your production tables for kicks?
SQL Injection: It happens
Listen folks, I know that security can be a pain but it’s there for a reason. Don’t get lazy and just assume the user needs an admin account to access the database because 9/10 times it doesn’t. You could probably get by fine on creating a new schema with write/read access and maybe EXECUTE stored procedures permissions. In fact, secure yourself from SQL injection attacks by wrapping your code in stored procedures in the first place.
There’s a ton of resources out there to learn how to properly secure SQL Server. Get up to speed by reading up about . There’s also tons of videos and demonstrations out there. Check out the courtesy of() and (). Finally (WARNING: Blatent self-promotion inbound) make sure to check out SQL University’s security week from Semester 1.聽Bottom line is if someone tells you “this application needs to run as sa”, have them give you a detailed explanation as to why. Part of your job as a responsible DBA is to protect your data and your聽database servers. If they simply don’t know any better then offer to educate them on schemas, security groups,聽etc.聽聽And remember, “because its an optimization” is a stupid answer.
Over the last few months I have been doing the rounds at various user groups and SQL Saturday events presenting on Policy Based Management. In the spirit of my on-going SQL University project as well as the upcoming book I’m co-authoring with Ken Simmons (| ) and Colin Stasiuk ( | ), I’ve thrown together this brief video walk-through on Policy Based Management.
Welcome to the first day of SQL University. Today we鈥檙e going to be talking about basic tools you鈥檒l be using as a database administrator (DBA).
Throughout our lessons you will notice I will be linking heavily to SQL Server Books Online. Books Online is the official documentation for all things SQL Server. This is important to know as many administrators and developers refer to this documentation on a daily basis as well as in everyday conversation. You can access Books Online in one of two ways. One is directly or you can actually download Books Online () so that you can access and refer to the documentation even when no network access is available. While downloading it for offline use can be beneficial (and portable) be aware that Microsoft does update Books Online with new information which means you would have to download and install the latest version of Books Online when this occurs. The good news is that when you use the local version of Books Online it does ask you up front if you want to use the internet as the first point of reference. Another advantage of having Books Online locally installed is that you can bookmark topics and searches so you can save time if you find yourself referring to a certain topic (which I can almost guarantee you will!). That being said make sure you explore the various links given to fully get the most out of the content delivered here at SQL U.
The most basic tool in the SQL Server toolset for an administrator or developer is a management graphical interface called the SQL Server Management Studio (SSMS). SSMS is where you can access, configure, manage and administrate your servers. The following video walks you through the basics of SSMS so you can become familiar with it. Before you watch the video there are a few things you need to know about SQL Server.
Authentication
In order to connect to a server or database you need to provide it valid credentials. This method is referred to as authentication. SQL Server recognizes two different types of authentication: Windows authentication and SQL Server Authentication. Windows authentication (sometimes also referred to as Integrated Security)聽 is when you provide SQL Server Windows account credentials. This can be either a Windows domain account (i.e. domainusername) or a local Windows account (i.e. local-machineusername). By default Windows authentication is the default authentication method selected when you open SSMS, and of note, is also more secure. We鈥檒l get in to the hows and whys of that in another class. When you open SSMS, the Windows credentials for the account you are logged into the machine as will automatically pass to SSMS. For instance if I鈥檓 logged into my computer as a user called JSEGARRA, that is on a domain called MSDOMAIN, SSMS will open and you will see in the box for username (will be greyed out) MSDOMAINJSEGARRA.
The second method of authentication is SQL Server Authentication (sometimes also referred to as just SQL Authentication). This method of authentication is useful for instances that, for whatever reason, do not have access to a Windows domain account or just a domain in general. SQL accounts are created and kept within the database instance itself. 聽An example of聽a use for this聽type of authentication method聽would be a database server that resides outside of a company firewall so that the public needs to get to it. Typically these servers are kept in what’s called the DMZ (demilitarized zone), which is an area that belongs to the company but is segregated from the internal network for security reasons. Since the DMZ is outside of the normal network you wouldn’t be able to authenticate with a domain account so instead we use local credentials like a SQL account.
Best of Both Worlds
For those curious, yes you CAN have both Windows authentication and SQL authentication enabled on your database server. This mode is called Mixed mode since you’re mixing both types of authentication methods. Be aware, however, that this increases your attack surface as you’re opening more holes to access your database server. Microsoft best practices聽recommend using Windows authentication for security reasons (account is managed at domain level, leverage AD groups, etc.).
Video: Walkthrough of SSMS pt 1. (9:02)
Warning: Video is hosted by YouTube. If you cannot see it your company might be blocking that site. My apologies, I will have an alternative method available in future.
Earlier I had blogged about the toolkit being available for Windows 7 RC but now Microsoft has officially released the RTM version of the tools. Big thanks to my co-worker Nick Piccone ( ) for bringing this to my attention. The new tools are available at the Microsoft site at the link below:
Overview
Remote Server Administration Tools for Windows 7 enables IT administrators to manage roles and features that are installed on remote computers that are running Windows Server 2008 R2 (and, for some roles and features, Windows Server 2008 or Windows Server 2003) from a remote computer that is running Windows 7. It includes support for remote management of computers that are running either the Server Core or full installation options of Windows Server 2008 R2, and for some roles and features, Windows Server 2008. Some roles and features on Windows Server 2003 can be managed remotely by using Remote Server Administration Tools for Windows 7, although the Server Core installation option is not available with the Windows Server 2003 operating system.
This feature is comparable in functionality to the Windows Server 2003 Administrative Tools Pack and Remote Server Administration Tools for Windows Vista with Service Pack 1 (SP1).
If you鈥檙e like me and trying out the Windows 7 RC on your everyday machines, there are certain key tools you find you need to do your work. For us system administrators Remote Server Administration Tools is definitely (or should be) one of those. I installed the previous version of tools on my workstation and it blew up on me (whoops!). Thankfully Microsoft, in their infinite wisdom, has released a version of this software for us Windows 7 folks! Just follow the directions on the page to figure out how to install the tools.
UPDATE: The RTM version of the tools have been released! Check it out