Is it Knoppix or PCLinuxOS time?

September 19, 2006

Not yet tired of windows? Well, read on to find out the next gen O/S out there…

* New capabilities – creating a remastered custom Live CD/DVD and booting from a USB flash

Let’s see where do I start…

Well, my machine crashed – it’s a new Acer – http://global.acer.com/4152 NLCI (4150 or 4650 series) laptop- and the problem was maybe some virus, because the ntoskrnl.exe was corrupted, and the drivers just stopped loading after a few seconds of boot time, bringing the hard disc to a halt and the screen to a frozen white screen (talk about the old blue screen being upgraded by MS!!!)

Time to look around for a boot-able and repair kit right! I tried – got an XP cd and installed XP onto a different partition – but the next day – that too went into the same loop like the above.

Then, I found out that maybe the hard disc may have got some permanent damage – thanks to all the travel lately.

I look around for a solution other than DOS obviously and I find the best free O/S ever!

Knoppix — the free open source Linux O/S that boots and runs from a CD/DVD (like PCLinuxOS 2007), with automatic hardware detection, recognizing all the devices – Graphics Card, Sound Card, USB, CD, DVD, RAID, Modem, Lan Card, printer-scanner(hp all-in-one), pcmcia cards, sd card(gateway works with PCLinuxOS), bluetooth(works with PCLinuxOS), wireless(works with PCLinuxOS),modem, external camera, webcam – you name it and its a solid system that detects, installs and boots fast (PCLinuxOS 2007 boots in 30 seconds), and runs quickly on ordinary hardware! It can even mount my NTFS hard disc in read mode (ntfs-3g driver supports read and write of NTFS files) to copy files to my USB so my critical files are saved in no time. Of course, part of the recovery process is being able to write CD and DVD’s which Knoppix (and PCLinuxOS) supports with a right click menu.

Knoppix – a flavor of Debian Linux – http://www.knopper.net/knoppix/index-en.html or http://www.knoppix.net/ – is a 640MB bootable Live CD with a collection of GNU/Linux s/w, which brings up a Windows like user interface, connects to the internet (after a minor config) through a DSL or Cable modem and voila – I’m online via Mozilla, Opera or Konqueror. Knoppix 5.1.1+ is a debian flavour of the Linux O/s with the Linux kernel 2.6.19+ , KDE 3.5.5+/GNOME 2.16+, ntfs-3g and it has all the flavours of a true windows system with OpenOffice2.1+ – (Word-documents,Excel-spreadsheets,PPT-presentations) , PDF reader, kaffeine (media player) and GIMP (for those mspaint users), picasa from google, a host of ntfs data recovery tools and good card games!

PCLinuxOS 2007 TR4is a free open source Linux O/S (flavor of Mandrake/Mandriva Linux with Linux kernel 2.6.22.10+, KDE 3.5.7+/GNOME 2.16+, Open Office 2.3+, 3D windows support), has all the above software modules, and it is much better than the Knoppix version as of PCLinuxOS 2007 TR4 versus Knoppix 5.1.1 – since PCLOS can be remastered and installed on a usb flash drive very easily.

Live CD Knoppix (like Live CD PCLinuxOS) uses on-the-fly decompression to load into memory, the required modules, from bootable CD/DVD, so the CD is locked by knoppix and you can’t use it for writing or DVD viewing although they have the CD/DVD writer software and the movie viewer software and I can use an external DVD/CD writer/reader to perform CD/DVD burns/reads. You can install the PCLinuxOS 2007 TR4 Live CD to a USB flash drive The other pluses to these Live CDs are: There is already an available messenger – thanks to secure GAIM (now Pidgin) – which can connect to Yahoo, Google Chat, AOL, etc. among others. Kopete is better than other messengers due to WebCam support, but need Knoppix/PCLinux installed to the hard disc. You can also create custom bootable CD/DVDs/USB flash, since the default live CDs are built for a read-only O/s (from the CD) with default options.

Want to listen to quality music? – using StreamTuner on Linux you can listen to live internet streamed (128 to 256kbps) quality radio on xmms, from around the World for free!!!

Security – you can install the necessary Mozilla addon’s and the shorewall or firestarter firewall to boost your experience.
VLC, Gxine, Amarok and Mplayer are very good multimedia programs covering all the needed experience.

Acrobat 8 is available for Linux for the pdf community.

Open Office or Star Office are not perfect but are decent Linux office solutions.

If you are wondering about install time – there is none – since the OS just boots of a CD/USB, you can use all the features of a full fledged O/S, and if you need, you may install the O/S to hard disc in 10 minutes.

So what are the minimum requirements of this new O/s (Vista beware!)

· Intel-compatible CPU (i486 or later),
· 32 MB of RAM for text mode, at least 96 MB for graphics mode with KDE (at least 128 MB of RAM is recommended to use the various office products),
· boot-able CD-ROM drive, or a boot floppy and standard CD-ROM (IDE/ATAPI or SCSI),
· standard SVGA-compatible graphics card,
· serial or PS/2 standard mouse or IMPS/2-compatible USB-mouse.

Before you comment, please note:

  • I know I could have called Acer – http://global.acer.com/ support – since my laptop is within warranty – well, I didn’t call because I needed internet connectivity – not further delays and postal issues with mailing my hard disc out.
  • Knoppix and PCLinuxOS have very good multi-session KDE3.5+/GNOME 2.16+ windows environment which I wanted compared to a lite-r environment like DSL Linux (http://www.damnsmalllinux.org/ – that’s another topic – 50MB linux – usb/mini-cd boot) – another reason not to go for a usb boot was the laptop bios was not upgraded by the manufacturer and the way to upgrade bios is only through a working Windows XP !! Latest: I finally got a usb floppy drive and got my BIOS upgraded, also I created a custom Live CD of PCLinuxOS 2007 TR4 and copied it to a USB flash which is now bootable (don’t forget to install th MBR) and working for Acer 4152 and Gateway MX 6124 laptops.
  • I downloaded Knoppix 3.6 from a friends cable connection and burnt the CD in 10 minutes and was online on my Acer 4152 NLCI laptop in 15 minutes: Latest: I downloaded Knoppix 5.1.1 Live DVD and PCLinuxOS 2007 TR4 Live DVD – PCLinuxOS rates better with custom Live DVD/USB flash boot support.
  • All of the needed drivers and software including Gaim, Office(Word,Excel,PPT), PDF viewer, printer-scanner drivers, rss reader, were on the CD so nothing to install – unlike ms windows, where a plain o/s is useless!!!

Knoppix 3.6 Problems and PCLinuxOS 2007 TR4 Advantages:

  • The laptop CD/DVD drive is being fully used/locked and I cannot eject it – when I log into the Knoppix Live O/s. This is by design, since the modules are dynamically loaded from the CD. You can burn custom CD’s by downloading the necessary softwares by apt-get
  • I downloaded PCLinuxOS 0.93 and later upgraded to PCLinuxOS 2007 TR4 and they install to hard disc easily with a single click, also they have a neat way to create a custom Live CD/DVD, which can be created in 20 minutes flat using mklivecd/k3b
  • Webcam with messenger is an issue with gaim – PCLinuxOS 2007 fixed this problem with a new version of Kopete and many more web camera tools.
  • WPA Wireless security is not supported with default ndiswrapper, you may still have to use the windows wireless card drivers – otherwise PCLinuxOS 2007 makes secure wireless a breeze, with support for secured 128 bit WEP.
  • The apt-get feature (combined with Synaptic of PCLinuxOS) of most Linux flavors (yast in OpenSuse) is great to keep your specific O/S features upto-date and remove broken packages. The control you have over your custom machine software is simply great.
  • I can’t access the SD Card inserted in the proprietary Acer Laptop Texas Instruments SD/MMC Card Reader – but I can connect the Kodak digital camera via USB and the photos can be uploaded. Latest: PCLinuxOS 2007 TR4 has a fix for many Gateway laptop SD card reader issues in their support forums for most SD cards and multi card readers.
  • I can’t read MS Visio documents (but this in development and I will use visio converted to jpegs till then).
  • Open Office is not yet a very mature software and cannot reasonably compare to MS Office 2003 in either features or printing of Excel documents.
  • PCLinuxOS 2007 has Beryl which is a decent 3D windows manager with nice 3D windows effects – but 3D windows is still not a very refined concept and I would suggest uninstalling the compiz and beryl 3D software.

Well, that’s it from me, see ya… Keep your Knoppix CD/DVD or PCLinuxOS CD/DVD/USB ready…. as we say in Linux there is true consumer choice even though I personally vote for PCLinuxOS 2007 TR4

Oh ya.. Knoppix supports clusters and a multi-computer version is out called “ParallelKnoppix” which converts a host of windows machines into a Linux Cluster Farm. Descriptions are here – http://idea.uab.es/mcreel/ParallelKnoppix/ http://www.knoppix.net/wiki/Cluster_Live_CD

Howto? – Another useful site to learn to use Linux – http://www.linux.ie/articles/tutorials/

Some more flavors that receive good desktop Linux reviews are

Other good sites with helpful linux links:


Migrating to ASP.NET 2.0 — Its backward compatible

October 21, 2005

Here are my investigations based on MSDN and a running site at Microsoft since Aug 2005 with better performance than before:

· Because of the way that the .NET Framework is designed, you can deploy the 2.0 framework without disrupting a current installation of the 1.0 or 1.1 frameworks.

To configure a 1.x application’s script map to use the .NET Framework version 2.0

  • On the Start menu, click Run.
  • In the Open box, type inetmgr and click OK.
  • In Internet Information Services (IIS) Manager, expand the local computer, and then expand Web Sites.
  • Select the target Web site that is running in the .NET Framework version 1.x.
  • Right-click the name of the virtual directory for the Web site, and then click Properties.
    The Properties dialog box appears.
  • In the ASP.NET version selection list, choose the .NET Framework version 2.0.
    Click OK.
  • Navigate to a page in your application and confirm that your application runs as expected.

· If you are planning on using ASP.NET 2.0 on a production site, you will need to acquire the Microsoft Visual Studio 2005 Beta 2 Go-Live license (Nov 2005 is the final release of VS .NET 2005, so this may change) http://lab.msdn.microsoft.com/ or http://msdn2.microsoft.com/ . Basically, Microsoft does not offer support for the pre-release products.
· ASP.NET 2.0 and ASP.NET 1.1 Applications can live on the same IIS Server: By default, your 1.x applications will continue to use the 1.x framework. However, you will have to configure your converted/new applications (web sites/virtual directories) to use the 2.0 framework.
· Requirements for hosting ASP.NET 2.0 Apps:
o Internet Information Services (IIS) version 5.0 or later. To access the features of ASP.NET, IIS with the latest security updates must be installed prior to installing the .NET Framework. (So you can run ASP.NET 2.0 apps on old boxes with IIS5-Win 2000 Server)
o ASP.NET is supported only on the following platforms: Microsoft Windows 2000 Professional (Service Pack 3 recommended), Microsoft Windows 2000 Server (Service Pack 3 recommended), Microsoft Windows XP Professional, and Microsoft Windows Server 2003 family.
o Microsoft Data Access Components 2.8; is recommended. This is for applications that use data access.
o Supported Operating Systems: Windows 2000; Windows 98; Windows 98 Second Edition; Windows ME; Windows Server 2003; Windows XP. Make sure you have the latest service pack and critical updates for the version of Windows that you are running. To find recent security updates, visit Windows Update.
o You must also be running Microsoft Internet Explorer 5.01 or later for all installations of the .NET Framework. Install Internet Explorer 6.0 Service Pack 1.

Here’s what we gain:
New Features in ASP.NET 2.0
· Master pages are a new feature introduced in ASP.NET 2.0 to help you reduce development time for Web applications by defining a single location to maintain a consistent look and feel in a site. Master pages allow you to design a template that can be used to generate a common layout for many pages in the application.
· Content pages (I call them business logic sub-pages) are attached to a master-page and define content for any ContentPlaceHolder controls in the master page. The content page contains controls that reference the controls in the master page through the ContentPlaceHolder ID. The content pages and the master page combine to form a single response.
· Nested Master Pages: In certain instances, master pages must be nested to achieve increased control over site layout and style. For example, your company may have a Web site that has a constant header and footer for every page, but your accounting department has a slightly different template than your IT department.
· Overriding Master Pages: Although the goal of master pages is to create a constant look and feel for all of the pages in your application, there may be situations when you need to override certain content on a specific page. To override content in a content page, you can simply use a content control.
· Themes and Skins: ASP.NET 2.0 rectifies the issue of using CSS and inline styles in ASP.NET 1.1 pages, through the use of themes and skins, which are applied uniformly across every page and control in a Web site.A skin is a set of properties and templates that can be used to standardize the size, font, and other characteristics of controls on a page. Themes are similar to CSS style sheets in that both themes and style sheets define a set of common attributes that apply to any page where the theme or style sheet is applied.
· Security: Managing User Info with Profiles and Login Controls: The membership provider and login controls in ASP.NET 2.0 provide a unified way of managing user information. ASP.NET 2.0 offers new login controls to help create and manage user accounts without writing any code.The ASP.NET 2.0 profile features allow you to define, save, and retrieve information associated with any user that visits your Web site. In a traditional ASP application, you would have to develop your own code to gather the data about the user, store it in session during the user’s session, and save it to some persistent data store when the user leaves the Web site.
· Localizaton. Enabling globalization and localization in Web sites today is difficult, requiring large amounts of custom code and resources. ASP.NET 2.0 and Visual Studio 2005 provide tools and infrastructure to easily build Localizable site including the ability to auto-detect incoming locale’s and display the appropriate locale based UI. Visual Studio 2005 includes built-in tools to dynamically generate resource files and localization references. Together, building localized applications becomes a simple and integrated part of the development experience.
· 64-Bit Support. ASP.NET 2.0 is now 64-bit enabled, meaning it can take advantage of the full memory address space of new 64-bit processors and servers. Developers can simply copy existing 32-bit ASP.NET applications onto a 64-bit ASP.NET 2.0 server and have them automatically be JIT compiled and executed as native 64-bit applications (no source code changes or manual re-compile are required).
· Caching Improvements. ASP.NET 2.0 also now includes automatic database server cache invalidation. This powerful and easy-to-use feature allows developers to aggressively output cache database-driven page and partial page content within a site and have ASP.NET automatically invalidate these cache entries and refresh the content whenever the back-end database changes. Developers can now safely cache time-critical content for long periods without worrying about serving visitors stale data.
· Web Parts: Web Parts are modular components that can be included and arranged by the user to create a productive interface that is not cluttered with unnecessary details. The user can:
o Choose which parts to display.
o Configure the parts in any order or arrangement.
o Save the view from one Web session to the next.
o Customize the look of certain Web Parts.
· Better Development Environment: ASP.NET 2.0 continues in the footsteps of ASP.NET 1.x by providing a scalable, extensible, and configurable framework for Web application development. The core architecture of ASP.NET has changed to support a greater variety of options for compilation and deployment. As a developer, you will also notice that many of your primary tasks have been made easier by new controls, new wizards, and new features in Visual Studio 2005. Finally, ASP.NET 2.0 expands the palette of options even further by introducing revolutionary new controls for personalization, themes and skins, and master pages. All of these enhancements build on the ASP.NET 1.1 framework to provide an even better set of options for Web development within the .NET Framework.
· Last but not the least there’s a host of new language features that reduce code lines in .NET 2.0: What’s New in the C# 2.0 Language and Compiler
With the release of Visual Studio 2005, the C# language has been updated to version 2.0, which supports the following new features:
o Generics
Generic types are added to the language to enable programmers to achieve a high level of code reuse and enhanced performance for collection classes. Generic types can differ only by arity. Parameters can also be forced to be specific types. For more information, see Generic Type Parameters.

o Iterators
Iterators make it easier to dictate how a foreach loop will iterate over a collection’s contents.

o Partial Classes
Partial type definitions allow a single type, such as a class, to be split into multiple files. The Visual Studio designer uses this feature to separate its generated code from user code.

o Nullable Types
Nullable types allow a variable to contain a value that is undefined. Nullable types are useful when working with databases and other data structures that may contain elements that contain no specific values.

o Anonymous Methods
It is now possible to pass a block of code as a parameter. Anywhere a delegate is expected, a code block can be used instead: there is no need to define a new method.

o Namespace alias qualifier
The namespace alias qualifier (::) provides more control over accessing namespace members. The global :: alias allows access the root namespace that may be hidden by an entity in your code.

o Static Classes
Static classes are a safe and convenient way of declaring a class containing static methods that cannot be instantiated. In C# version 1.2 you would have defined the class constructor as private to prevent the class being instantiated.

o External Assembly Alias
Reference different versions of the same component, contained in the same assembly, with this expanded use of the extern keyword.

o Property Accessor Accessibility
It is now possible to define different levels of accessibility for the get and set accessors on properties.

o Covariance and Contravariance in Delegates
The method passed to a delegate may now have greater flexibility in its return type and parameters.

o How to: Declare, Instantiate, and Use a Delegate
Method group conversion provides a simplified syntax for declaring delegates.

o Fixed Size Buffers
In an unsafe code block, it is now possible to declare fixed-size structures with embedded arrays.

o Friend Assemblies
Assemblies can provide access to non-public types to other assemblies.

o Inline warning control
The #pragma warning directive may be used to disable and enable certain compiler warnings.

o volatile
The volatile keyword can now be applied to IntPtr and UIntPtr.

Thanks to the various links by Microsoft for the above info.
http://msdn2.microsoft.com/en-us/library/ms228038.aspx
http://msdn2.microsoft.com/en-us/library/ms228211.aspx
http://msdn2.microsoft.com/en-us/library/ms228097.aspx
http://msdn2.microsoft.com/en-us/library/7cz8t42e.aspx


SQL Server: @@IDENTITY deadlock problem and fix

November 18, 2004

This interesting problem occurs only when there is a call to update after the insert and the @@IDENTITY value has to be locked, so there is a deadlock trying to get a hold of this value.

CREATE TABLE [test]
(
[a] [int] IDENTITY (1, 1) NOT NULL ,
[b] [varchar] (10) NULL ,
[c] [int] NULL ,
CONSTRAINT [PK__test] PRIMARY KEY CLUSTERED ( [a] )
)
GO

Here [a] and [c] have to have the same value.

So, this programmer goes ahead and adds a trigger to do this on the insert operation.

CREATE TRIGGER test_update ON dbo.test
FOR INSERT
AS
begin
update dbo.test set c = a
end;

And, the insert statement called by two threads(client processes) simultaneously is:

insert into test(b) VALUES (‘test111’)

This leads to a deadlock and this “Error Message:”
“Exception Transaction Process (PID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.
Rerun the transaction”

The fix:
insert into test(b,c) VALUES (‘test111’,@@IDENTITY)

Notes:


Migrating Oracle Databases to SQL Server 2000

October 8, 2004

SQL Server 2000 only works on Windows-based platforms, including Windows 9x, Windows NT, Windows 2000 and Windows CE.
In comparison with SQL Server 2000, Oracle 9i Database supports all known platforms, including Windows-based platforms, AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, Sun Solaris and so on.

Sometimes there is the migration issue, so here goes…

There are some nice articles on Oracle to SQL Server migration at

http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part2/c0761.mspx

SQL Server vs Oracle Feature differences
http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm

There is a nice tool for the Migration of stored procs/SQL at
http://www.swissql.com/oracle-to-sql-server.html

Latest Top Ten TPC-C by PerformanceVersion 5 Results
http://www.tpc.org/tpcc/results/tpcc_perf_results.asp

Top Ten TPC-C by Price/PerformanceVersion 5 Results
http://www.tpc.org/tpcc/results/tpcc_price_perf_results.asp

Oracle vs SQL Server
http://www.dba-oracle.com/oracle_tips_oracle_v_sql_server.htm

Thanks to the authors of these public sites for the relevant information.


Testing SSL on Win Server 2003/IIS6

October 6, 2004

Hi there,

Been busy with lots of work with .NET Remoting Performance Testing and stuff.

Found something interesting so here goes.

There’s a nice easy way to test your IIS6 – SSL performance, install the free SelfSSL Certificate(SelfSSL Version 1.0) from the IIS Resource Kit (http://www.microsoft.com/downloads/details.aspx?FamilyID=56fc92ee-a71a-4c73-b628-ade629c89499&displaylang=en) . Its very easy to use (that’s what we look for right?) . Check out http://www.visualwin.com/SelfSSL/ for detailed directions on how to get your site into https (for testing only).

The following very useful (performance, analysis and deployment) tools are also available in the IIS6 Resource Kit package :

  • IISCertDeploy.vbs Version 1.0
  • Log Parser Version 2.1
  • Metabase Explorer Version 1.6
  • Permissions Verifier Version 1.0
  • Web Capacity Analysis Tool Version 5.2

Thanks to Microsoft and the authors of the http://www.visualwin.com/ site on which there is lots of other interesting info. on Win 2003 and IIS6.


Performance Tools

September 16, 2004

PERFORMANCE MONITOR — Counters; Understand thresholds
CLRPROFILER — Allocations; Survivors; Leaking (Another Profiler is from DevPartner)
WINDBG — Dumps; Hangs, Crashes, Blocks, Memory, etc
VADUMP — Working set; Memory, etc
NETMON — Data on Wire; Bandwidth and Latency


Securing SQL Server 2000 database and datafiles

September 16, 2004

Restrict physical access to the SQL Server computer. Always lock the server while not in use.
Make sure, all the file and disk shares on the SQL Server computer are read-only. In case you have read-write shares, make sure only the right people have access to those shares.
Use the NTFS file system as it provides advanced security and recovery features.
Prefer Windows authentication to mixed mode. If mixed mode authentication is inevitable, for backward compatibility reasons, make sure you have complex passwords for sa and all other SQL Server logins. It is recommended to have mixed case passwords with a few numbers and/or special characters, to counter the dictionary based password guessing tools and user identity spoofing by hackers.
Rename the Windows NT/2000 Administrator account on the SQL Server computer to discourage hackers from guessing the administrator password.
In a website environment, keep your databases on a different computer than the one running the web service. In other words, keep your SQL Server off the Internet, for security reasons.
Keep yourself up-to-date with the information on latest service packs and security patches released by Microsoft. Carefully evaluate the service packs and patches before applying them on the production SQL Server. Bookmark this page for the latest in the security area from Microsoft: http://www.microsoft.com/security/
If it is appropriate for your environment, hide the SQL Server service from appearing in the server enumeration box in Query Analyzer, using the /HIDDEN:YES switch of NET CONFIG SERVER command.
Enable login auditing at the Operating System and SQL Server level. Examine the audit for login failure events and look for trends to detect any possible intrusion.
If it fits your budget, use Intrusion Detection Systems (IDS), especially on high-risk online database servers. IDS can constantly analyze the inbound network traffic, look for trends and detect Denial of Service (DoS) attacks and port scans. IDS can be configured to alert the administrators upon detecting a particular trend.
Disable guest user account of Windows. Drop guest user from production databases using sp_dropuser
Do not let your applications query and manipulate your database directly using SELECT/INSERT/UPDATE/DELETE statements. Wrap these commands within stored procedures and let your applications call these stored procedures. This helps centralize business logic within the database, at the same time hides the internal database structure from client applications.
Let your users query views instead of giving them access to the underlying base tables.
Discourage applications from executing dynamic SQL statements. To execute a dynamic SQL statement, users need explicit permissions on the underlying tables. This defeats the purpose of restricting access to base tables using stored procedures and views.
Don’t let applications accept SQL commands from users and execute them against the database. This could be dangerous (known as SQL injection), as a skilled user can input commands that can destroy the data or gain unauthorized access to sensitive information.
Take advantage of the fixed server and database roles by assigning users to the appropriate roles. You could also create custom database roles that suit your needs.
Carefully choose the members of the sysadmin role, as the members of the sysadmin role can do anything in the SQL Server. Note that, by default, the Windows NT/2000 local administrators group is a part of the sysadmin fixed server role.
Constantly monitor error logs and event logs for security related alerts and errors.
SQL Server error logs can reveal a great deal of information about your server. So, secure your error logs by using NTFS permissions.
Secure your registry by restricting access to the SQL Server specific registry keys like HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer.
If your databases contain sensitive information, consider encrypting the sensitive pieces (like credit card numbers and Social Security Numbers (SSN)). There are undocumented encryption functions in SQL Server, but I wouldn’t recommend those. If you have the right skills available in your organization, develop your own encryption/decryption modules using Crypto API or other encryption libraries.
If you are running SQL Server 7.0, you could use the encryption capabilities of the Multi-Protocol net library for encrypted data exchange between the client and SQL Server. SQL Server 2000 supports encryption over all protocols using Secure Socket Layer (SSL). See SQL Server 7.0 and 2000 Books Online (BOL) for more information on this topic. Please note that, enabling encryption is always a tradeoff between security and performance, because of the additional overhead of encryption and decryption.
Prevent unauthorized access to linked servers by deleting the linked server entries that are no longer needed. Pay special attention to the login mapping between the local and remote servers. Use logins with the bare minimum privileges for configuring linked servers.
DBAs generally tend to run SQL Server service using a domain administrator account. That is asking for trouble. A malicious SQL Server user could take advantage of these domain admin privileges. Most of the times, a local administrator account would be more than enough for SQL Server service.
DBAs also tend to drop system stored procedures like xp_cmdshell and all the OLE automation stored procedures (sp_OACreate and the likes). Instead of dropping these procedures, deny EXECUTE permission on them to specific users/roles. Dropping these procedures would break some of the SQL Server functionality.
Be prompt in dropping the SQL Server logins of employees leaving the organization. Especially, in the case of a layoff, drop the logins of those poor souls ASAP as they could do anything to your data out of frustration.
When using mixed mode authentication, consider customizing the system stored procedure sp_password, to prevent users from using simple and easy-to-guess passwords.
To setup secure data replication over Internet or Wide Area Networks (WAN), implement Virtual Private Networks (VPN) . Securing the snapshot folder is important too, as the snapshot agent exports data and object scripts from published databases to this folder in the form of text files. Only the replication agents should have access to the snapshot folder.
It is good to have a tool like Lumigent Log Explorer handy, for a closer look at the transaction log to see who is doing what in the database.
Do not save passwords in your .udf files, as the password gets stored in clear text.
If your database code is proprietary, encrypt the definition of stored procedures, triggers, views and user defined functions using the WITH ENCRYPTION clause. dbLockdown is a tool that automates the insertion of the WITH ENCRYPTION clause and handles all the archiving of encrypted database objects so that they can be restored again in a single click. Click here to find out more information about this product.
In database development environments, use a source code control system like Visual Source Safe (VSS) or Rational Clear Case. Control access to source code by creating users in VSS and giving permissions by project. Reserve the ‘destroy permanently’ permission for VSS administrator only. After project completion, lock your VSS database or leave your developers with just read-only access.
Store the data files generated by DTS or BCP in a secure folder/share and delete these files once you are done.
Install anti-virus software on the SQL Server computer, but exclude your database folders from regular scans. Keep your anti-virus signature files up to date.
SQL Server 2000 allows you to specify a password for backups. If a backup is created with a password, you must provide that password to restore from that backup. This discourages unauthorized access to backup files.
Windows 2000 introduced Encrypted File System (EFS) that allows you to encrypt individual files and folders on an NTFS partition. Use this feature to encrypt your SQL Server database files. You must encrypt the files using the service account of SQL Server. When you want to change the service account of SQL Server, you must decrypt the files, change the service account and encrypt the files again with the new service account.