How to login to SQL Server as SA while running Windows Authentiaction only mode

A colleague of mine ran into following situation:
My Company polocies enforce that domain-admins group be removed from sysadmin role in production SQL Server and windows authentication mode should only be used.
He removed the domain-admin group from sysadmin roles , then set windows authentication only and restarted the server at night.
When he tried to log in back with his domain user account (which was part of domain admin group too) he could only log on to master db with public role only!!!!!!!!!!!!!!!!!!!!!
He forgot to add his own account explicitly in SQL Server before removing domain-admins group.

Interesting situation:

• Domain-admins removed from SQL sysadmin role
• SQL running Windows Authentication only Mode.
• No windows user exist in SQL with sysadmin role.

Steps to log in back to SQL with sysadmin rights using SA?
Besides my colleague was planning to run rebuildm.exe utility to rebuild the master db , and then setting up logins and all user databases using scripts.
I tried something on my local test machine and found that there's a way!

I opened Enterprise Manager and tried to connect using SA with SQL authentication; Error: Login Failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.
hmmmm! OK
Solution:

• I logged into the operating system running production SQL machine using local admin account.
• Checked that NAMED-PIPES are enabled under the Server network utility.
• Start --> Program Files --> Microsoft SQL Server --> SQL Server Client Network Utility
• Goto Alias, Select Add.
• Under network libaries , selected NAMED PIPES
• Gave server alias prod1
• Under Server Name typed the actual SQL instance name (in our case PRODUK1)
• Clicked OK.
• Open the Enterprise manager again
• New SQL Server Registration.
• Under the server, typed the alias: prod1
• Selected, Use SQL Server authentication
• user name : SA
• password : typed the password
• clicked OK.

Successfully connected using sa account! :)

This isn't a security breach; since you already logged in to the machine locally, that means your are an authenticated user to the system. NamedPipes library only supports connections that are valid windows logins. You can not connect using NamedPipes over the WAN or out of a trusted domain. Your Windows login must be authenticated on the machine running SQL Server before in order to use NamedPipes!


Cheers!

Hot News SQL 2005! SP3

Sick of applying KBArticles, patches and updating binaries. :(

Finally microsoft seems in approval of delivering SQL 2005 Service Pack3 by fourth quarter of 2008.
Yuppeee!!!!!

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/04/15/sql-server-2005-sp3-is-publicly-confirmed.aspx

http://blogs.technet.com/dataplatforminsider/archive/2008/04/15/sql-server-2005-sp3-coming-soon.aspx

RAM and procedure cache issue resolved in SQL 2008 (Thanx to Adam Mechanic) :)

lack of RAM control and inefficiencies in SQL Procedure Cache very well addressed and escallated by the SQL Guru!
Microsoft introducing Resource Governer in sql 2008 sates that you can control the amount of RAM Proc Cache can acquire by defining resource pools. Details below:

http://sqlblog.com/blogs/adam_machanic/archive/2008/04/15/sql-server-procedure-cache-more-relief-on-the-way.aspx

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=293188

Career as a DBA

Database Administration comes into play in medium large and large enpterprises and organizations running database server boxes where the business can not tolerate downtime and data availability is very critical.
Such setups involve expensive hardware and an enterprise class DBMS solution , eg., MSSQL Enteprise Edition, ORACLE Enterprise Edition etc.
Usually the server boxes include SAN(Storage Area Network) or NAS(Network Area Storage) setup as clusters and connected to multiple active/active or active/passive server boxes. The SAN usually connects to the server through a Host Bus Adapter and the medium could be Fiber Channel or some other fast communication channel.
NAS connects to the server via a Switch and has much more flexibility of placing it physically farther than the servers.
The disk drives in NAS, SAN, or the Server itself are fast SATA or SCSIs disks bundled up in different level of hardware RAIDS for faster read/writes and fault tolerance.
Many people focus more on multiple high speed processors with not quite edequate L2 Cache. As a general rule of thumb, a PIII processor running on a board with greator L2 Cache would outperform a P4 Processor running with inadequate L2 Cache.
A DBAs task also involves selecting and configuring RAIDs to serve different kind of I/O activity; example is that you require different RAID for sequential Reads/Writes and different for non sequential Reads/Writes. One also needs to accommodate the fault tolerance.
Technically speaking, DBAs are most prone to waking up nights and staying long at office for backups , performance and downtime issues. The business stucks if the db server goes down; that adds your value to your organization more than a Developer(no offence). DBA is a hot and fun job, but you also take stress along with you at home with a fear of being called late night for assistance:)

A DBA tasks include:
Disaster Recovery Plans (including backups, restore testing)
Performance Tuning (Includes hardware and Server performance monitoring, SQL optimization)
Development (TSQL or PLSQL, whatever dbms you have)
Security (Audits, user roles and permissions, patterns of transactions etc)
Reporting (Reports server, including automation of reports for management and staff)

As of my past experience Databases are most prone to DBAs than any thing; to err is human :). I don't believe if any of the db gurus never caught up in a disasterous situation of a messed up database, the quality that seperates them from ordinary db guys is, how confidently they react by keeping the nerves in control and resolving the problem in best possible way with minimum down time.
Many smart and compitent Tecchy folks turn to the DARK END; the so called MANAGEMENT. As it's said, when you get technically experienced and older in your field; your technical skills become secondary to your management skills. Your spoken and writting skills, dressing and attitude decides how far you'll go.
Or in other words; Degree of Management Skills is INVERSELY proportional to that of Technical Skills.:)


Cheers!

SQL Machine in a workgroup; unable to access network resources!

In order for SQL server to access network resrources(eg. taking backups etc on network paths) with in a domain, we are aware that SQL Startup Agent account should be a domain user with appropriate rights within the LAN.
Howerver, What if SQL Server machine is not part of Domain and you need to BCP out a file to your webserver which is in the LAN but both machines are in a workgroup?
Here's a solution:

1. Create a share folder on Destination Network(webserver).
2. MAP a drive on your SQL machine to the shared folder you created.
3. Run your SQL command (eg backup or bcp) with the security context of a Windows user account, not SQL user. The actual problem lies here; using SA won't work, it will give you error that network path not accessible. You have to use windows user account in SQL to acess the network resources in case machine running SQL Server is not a part of DOMAIN.

Note: You can ommit the step 2 and use UNC naming convention to access the path directly in the code ( eg. backup database mydb to disk = '\\webserver\shared\mydb.bak'), but it's better to use MAP-Drives for robust coding.

SQL Server 2008 Reporting Services!

Check out below link to find what's behind the Blissful and promising SQL Server 2008 Reporting Services! ;)
http://www.dundas.com/Company/Media/PressSQL2008.aspx