some very useful Query Analyzer Shortcut Keys in SQL Server .
Keyboard Shortcuts in SQL Query Analyzer
CTRL-SHIFT-F2 -- Clear all bookmarks. CTRL+F2 -- Insert or remove a bookmark (toggle). F2 -- Move to next bookmark. SHIFT+F2 -- Move to previous bookmark. ALT+BREAK -- Cancel a query. CTRL+O -- Connect. CTRL+F4 -- Disconnect. CTRL+F4 -- Disconnect and close child window. ALT+F1 -- Database object information. CTRL+SHIFT+DEL -- Clear the active Editor pane. CTRL+SHIFT+C -- Comment out code. CTRL+C or Ctrl+Insert -- CopyCTRL+X or Shift+Del -- CutSHIFT+TAB -- Decrease indent. CTRL+DEL -- Delete through the end of a line in the Editor pane. CTRL+F -- Find. CTRL+G -- Go to a line number. TAB -- Increase indent. CTRL+SHIFT+L -- Make selection lowercase. CTRL+SHIFT+U -- Make selection uppercase. CTRL+V or Shift+Insert -- Paste. CTRL+SHIFT+R -- Remove comments. F3 -- Repeat last search or find next. CTRL+H -- Replace. CTRL+A -- Select all. CTRL+Z -- Undo. F5 or Ctrl + E -- Execute a query. F1 -- Help for Query Analyzer. SHIFT+F1 -- Help for the selected Transact-SQL statement. F6 -- Switch between query and result panes. Shift+F6 -- Switch panes. CTRL+W -- Window Selector. CTRL+N -- New Query window. F8 -- Object Browser (show/hide). F4 -- Object Search. CTRL+F5 -- Parse the query and check syntax. CTRL+P -- PrintCTRL+D -- Display results in grid format. CTRL+T -- Display results in text format. CTRL+B -- Move the splitter. CTRL+SHIFT+F -- Save results to file. CTRL+R -- Show Results pane (toggle). CTRL+S -- SaveCTRL+SHIFT+INSERT -- Insert a template. CTRL+SHIFT+M -- Replace template parameters. CTRL+L -- Display estimated execution plan. CTRL+K -- Display execution plan (toggle ON/OFF). CTRL+I -- Index Tuning Wizard. CTRL+SHIFT+S -- Show client statistics CTRL+SHIFT+T -- Show server trace. CTRL+U -- Use database
Tuesday, December 7, 2010
Friday, December 3, 2010
Running SQL Server Agent with a least privilege service account
Problem
For improved security Microsoft recommends the SQL Server Agent service account should not be a member of the local Administrators group. Being a member of the Administrator group, grants the account super-user privileges which therefore may expose you to more security vulnerabilities. By limiting access for the service accounts it will help you safeguard your system if individual services or processes are compromised.
Solution
As a best practice, SQL Server Agent service account rights should be kept as low as possible to prevent exposing your system to security risks. Making the SQL Server service account an administrator, at either a server level or a domain level, grants too many unneeded privileges and should never be done. Ideally, all the SQL Server services should run from a different account and each account should have exactly the privileges that it needs to do its job and no additional privileges.
During a new installation, SQL Server setup does not default the SQL Server engine service and SQL Server Agent service to any account. The account specification is a required step for these services. Using a local user or domain user that is not a Windows administrator is the best choice.
If the server that is running SQL Server is part of a domain and needs to access domain resources, such as file shares or uses linked server connections to other computers running SQL Server, a domain account should be used. If the server is not part of a domain, a local user that is not a Windows administrator is preferred.
The SQL Server Agent service account requires sysadmin privileges in the SQL Server instance that it is associated with. In this tip I have tried to put forth a solution by running SQL Server agent under group (SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER). This group has all the required privileges and is not part of the administrator group.
NOTE: When you install Microsoft SQL Server to run using a Microsoft Windows NT account, SQL Server sets various Windows user rights and permissions on certain files, folders, and registry keys for that account. If you later change the startup account for the SQL Server Agent service using SQL Server Configuration Manager, SQL Server automatically assigns all the required permissions and Windows user rights to the new account for you, so that you do not have to do anything else.
Steps to SQL Agent Account
STEP 1
Add the account under which you want to run the SQL Server agent service in the SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER group. (Right click my computer -> Manage -> Local Users and Groups -> Groups). Then find the group, right click on it and select Properties. This group is pre-configured with all the required permissions to run the SQL Agent service. Also, make sure the account you add to thsi group is not a member of the local administrator group. In this example I am adding "Agent test" to this group.
STEP 2
Change the log on account of the SQL Server Agent service in SQL Server Configuration Manager in SQL Server 2005. Use the account that you just added to the SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER group, in my case it is "Agent test". You may also consider doing it through services.msc, but it is recommended to do it using SQL Server Configuration Manager. The reason is, when you install SQL2005, a service master key for encryption is created. This key is then used to encrypt certificates and any other encryption keys. The service master key (SMK) is linked to the service account and changing this account can make the key invalid and then it can’t open the certificates anymore. When you change your service account through SQL Server Configuration Manager, SQL 2005 will take care of creating a SMK. By default, only members of the local administrators group can alter the service account, start, stop, pause, resume or restart a service.
STEP 3
Restart the SQL Server Agent service, so that the new account goes into affect.
STEP 4
You can check in SSMS that SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER group is a member of sysadmin role. In SSMS, go to Security -> Sever Roles. Right click on "sysadmin" and select Properties to view the scrreen below. If it is not already there, follow the steps in step 4a.
STEP 4a - add login and role
If this group is not already part of the sysadmin role, follow these steps.
Add the group SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER as a Windows authenticated login in SQL Server
And then assign the sysadmin role to this login.
Now SQL Server agent is running under an account which is not a member of the local administrators group on the server.
Note: there is limitation for using multiserver administration when the SQL Server Agent service account is not a member of the local Administrators group. Enlisting target servers to a master server may fail with the following error message: "The enlist operation failed." To resolve this error, restart both the SQL Server and the SQL Server Agent services.
For improved security Microsoft recommends the SQL Server Agent service account should not be a member of the local Administrators group. Being a member of the Administrator group, grants the account super-user privileges which therefore may expose you to more security vulnerabilities. By limiting access for the service accounts it will help you safeguard your system if individual services or processes are compromised.
Solution
As a best practice, SQL Server Agent service account rights should be kept as low as possible to prevent exposing your system to security risks. Making the SQL Server service account an administrator, at either a server level or a domain level, grants too many unneeded privileges and should never be done. Ideally, all the SQL Server services should run from a different account and each account should have exactly the privileges that it needs to do its job and no additional privileges.
During a new installation, SQL Server setup does not default the SQL Server engine service and SQL Server Agent service to any account. The account specification is a required step for these services. Using a local user or domain user that is not a Windows administrator is the best choice.
If the server that is running SQL Server is part of a domain and needs to access domain resources, such as file shares or uses linked server connections to other computers running SQL Server, a domain account should be used. If the server is not part of a domain, a local user that is not a Windows administrator is preferred.
The SQL Server Agent service account requires sysadmin privileges in the SQL Server instance that it is associated with. In this tip I have tried to put forth a solution by running SQL Server agent under group (SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER). This group has all the required privileges and is not part of the administrator group.
NOTE: When you install Microsoft SQL Server to run using a Microsoft Windows NT account, SQL Server sets various Windows user rights and permissions on certain files, folders, and registry keys for that account. If you later change the startup account for the SQL Server Agent service using SQL Server Configuration Manager, SQL Server automatically assigns all the required permissions and Windows user rights to the new account for you, so that you do not have to do anything else.
Steps to SQL Agent Account
STEP 1
Add the account under which you want to run the SQL Server agent service in the SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER group. (Right click my computer -> Manage -> Local Users and Groups -> Groups). Then find the group, right click on it and select Properties. This group is pre-configured with all the required permissions to run the SQL Agent service. Also, make sure the account you add to thsi group is not a member of the local administrator group. In this example I am adding "Agent test" to this group.
STEP 2
Change the log on account of the SQL Server Agent service in SQL Server Configuration Manager in SQL Server 2005. Use the account that you just added to the SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER group, in my case it is "Agent test". You may also consider doing it through services.msc, but it is recommended to do it using SQL Server Configuration Manager. The reason is, when you install SQL2005, a service master key for encryption is created. This key is then used to encrypt certificates and any other encryption keys. The service master key (SMK) is linked to the service account and changing this account can make the key invalid and then it can’t open the certificates anymore. When you change your service account through SQL Server Configuration Manager, SQL 2005 will take care of creating a SMK. By default, only members of the local administrators group can alter the service account, start, stop, pause, resume or restart a service.
STEP 3
Restart the SQL Server Agent service, so that the new account goes into affect.
STEP 4
You can check in SSMS that SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER group is a member of sysadmin role. In SSMS, go to Security -> Sever Roles. Right click on "sysadmin" and select Properties to view the scrreen below. If it is not already there, follow the steps in step 4a.
STEP 4a - add login and role
If this group is not already part of the sysadmin role, follow these steps.
Add the group SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER as a Windows authenticated login in SQL Server
And then assign the sysadmin role to this login.
Now SQL Server agent is running under an account which is not a member of the local administrators group on the server.
Note: there is limitation for using multiserver administration when the SQL Server Agent service account is not a member of the local Administrators group. Enlisting target servers to a master server may fail with the following error message: "The enlist operation failed." To resolve this error, restart both the SQL Server and the SQL Server Agent services.
Monday, April 5, 2010
ASP Codes to prevent MSSQL Injection Data Cleaner for Database Security
This is a simple input field cleaner to help prevent SQL Injection problems. SQL Injection occurs when a hacker fills out a form on your web site or posts data to a script. If the script which accepts and processes the data reads or writes to a SQL database, the hacker can include SQL commands.
Those commands can insert data to your database and/or can output details about the tables and fields in your database.
There is much more to SQL injection, but this simple script will help to clean submitted data. It is intended for alphanumeric fields. You can clean numeric fields separately by testing for isNumeric and Not isNull.
ASP FUNCTIONS CODES
Function fncInputDataCleaner(StringToClean)
If Len(StringToClean) > 0 Then
For x = 1 to 3
StringToClean = Replace(stringToClean, "'", "''")
StringToClean = Replace(stringToClean, "--", "-")
StringToClean = Replace(stringToClean, ";", " ")
StringToClean = Replace(stringToClean, "<", " ") StringToClean = Replace(stringToClean, ">", " ")
StringToClean = Replace(stringToClean, "%", " ")
StringToClean = Replace(stringToClean, " Next
End If
CleanInputData = stringToClean
End Function
Instructions
Add this function to your Classic ASP script or include it in a file with commonly used functions. Call the function like this:
strMyName = Request("MyName")
strMyName = fncInputDataCleaner(strMyName)
Those commands can insert data to your database and/or can output details about the tables and fields in your database.
There is much more to SQL injection, but this simple script will help to clean submitted data. It is intended for alphanumeric fields. You can clean numeric fields separately by testing for isNumeric and Not isNull.
ASP FUNCTIONS CODES
Function fncInputDataCleaner(StringToClean)
If Len(StringToClean) > 0 Then
For x = 1 to 3
StringToClean = Replace(stringToClean, "'", "''")
StringToClean = Replace(stringToClean, "--", "-")
StringToClean = Replace(stringToClean, ";", " ")
StringToClean = Replace(stringToClean, "<", " ") StringToClean = Replace(stringToClean, ">", " ")
StringToClean = Replace(stringToClean, "%", " ")
StringToClean = Replace(stringToClean, "
End If
CleanInputData = stringToClean
End Function
Instructions
Add this function to your Classic ASP script or include it in a file with commonly used functions. Call the function like this:
strMyName = Request("MyName")
strMyName = fncInputDataCleaner(strMyName)
Wednesday, March 3, 2010
How to find & Stop SQL injection attacks from happening
There’s a lot of stuff out there about SQL injection attacks , but there’s not much that will help you figure out how to stop these attacks from occurring.
First, let’s talk about what a SQL Injection Attack really is. Some people think it’s a virus of sorts, that is “inside your site.” Not the case. These are bot attacks by other virus infected computers. They simply use a brute force approach of scanning URLs that take POST/GET inputs and attempt to send their own data to them.
So, how do you track these down and stop them? For web sites powered by Microsoft’s IIS, here are our suggestions:
1.Look at your IIS logs
Try searching for the word “DECLARE” or “EXECUTE.” If you’ve been hit by an attack, these will more than likely show up in your IIS logs — at least for any attack that was attempted using “GET” posts. If you do find any instances of “DECLARE” or “EXECUTE” these are the pages to start with.
2.Use centralized database connection handling
Simple, make a centralized file (e.g. connection.asp if you are using ASP — see our free example) that handles all of your DB access. This way, it’s easier to make sure that you are SQL encoding your pages. You can easily search queries for “DECLARE” and “EXECUTE” and stop the attacks dead in their tracks.
3.Implement a site wide solution
If you are running your own server, we highly recommend ISAPI_Rewrite from HeliconTech (http://www.helicontech.com/isapi_rewrite). This is an ISAPI filter that allows you to do a variety of things, including scan URL data. This will stop 99% of attacks without changing ANY code on your site!
First, let’s talk about what a SQL Injection Attack really is. Some people think it’s a virus of sorts, that is “inside your site.” Not the case. These are bot attacks by other virus infected computers. They simply use a brute force approach of scanning URLs that take POST/GET inputs and attempt to send their own data to them.
So, how do you track these down and stop them? For web sites powered by Microsoft’s IIS, here are our suggestions:
1.Look at your IIS logs
Try searching for the word “DECLARE” or “EXECUTE.” If you’ve been hit by an attack, these will more than likely show up in your IIS logs — at least for any attack that was attempted using “GET” posts. If you do find any instances of “DECLARE” or “EXECUTE” these are the pages to start with.
2.Use centralized database connection handling
Simple, make a centralized file (e.g. connection.asp if you are using ASP — see our free example) that handles all of your DB access. This way, it’s easier to make sure that you are SQL encoding your pages. You can easily search queries for “DECLARE” and “EXECUTE” and stop the attacks dead in their tracks.
3.Implement a site wide solution
If you are running your own server, we highly recommend ISAPI_Rewrite from HeliconTech (http://www.helicontech.com/isapi_rewrite). This is an ISAPI filter that allows you to do a variety of things, including scan URL data. This will stop 99% of attacks without changing ANY code on your site!
Tuesday, March 2, 2010
SQL Server Management Studio Keyboard Shortcuts
SQL Server Management Studio offers users two keyboard schemes. By default, it uses the Standard scheme, with keyboard shortcuts based on Microsoft Visual Studio. A second scheme, called SQL Server 2000, closely resembles the tools from SQL Server 2000, in particular the keyboard shortcuts from the Query Analyzer. In a few cases, SQL Server Management Studio cannot offer the keyboard shortcuts from Query Analyzer. To change the keyboard scheme or add additional keyboard shortcuts, on the Tools menu, click Options. Select the desired keyboard scheme on the Environment, Keyboard page.
Menu Activation Keyboard Shortcuts
Action
Standard
SQL Server 2000
Move to the SQL Server Management Studio menu bar
ALT
ALT
Activate the menu for a tool component
ALT+HYPHEN
ALT+HYPHEN
Display the context menu
SHIFT+F10
SHIFT+F10
Display the New File dialog box to create a file
CTRL+N
No equivalent
Display the New Project dialog box to create a new project
CTRL+SHIFT+N
CTRL+SHIFT+N
Display the Open File dialog box to open an existing file
CTRL+O
CTRL+SHIFT+INS
Display the Open Project dialog box to open an existing project
CTRL+SHIFT+O
No equivalent
Display the Add New Item dialog box to add a new file to the current project
CTRL+SHIFT+A
No equivalent
Display the Add Existing Item dialog box to add an existing file to the current project
ALT+SHIFT+A
No equivalent
Display the Query Designer
CTRL+SHIFT+Q
CTRL+SHIFT+Q
Close a menu or dialog box, canceling the action
ESC
ESC
Menu Activation Keyboard Shortcuts
Action
Standard
SQL Server 2000
Move to the SQL Server Management Studio menu bar
ALT
ALT
Activate the menu for a tool component
ALT+HYPHEN
ALT+HYPHEN
Display the context menu
SHIFT+F10
SHIFT+F10
Display the New File dialog box to create a file
CTRL+N
No equivalent
Display the New Project dialog box to create a new project
CTRL+SHIFT+N
CTRL+SHIFT+N
Display the Open File dialog box to open an existing file
CTRL+O
CTRL+SHIFT+INS
Display the Open Project dialog box to open an existing project
CTRL+SHIFT+O
No equivalent
Display the Add New Item dialog box to add a new file to the current project
CTRL+SHIFT+A
No equivalent
Display the Add Existing Item dialog box to add an existing file to the current project
ALT+SHIFT+A
No equivalent
Display the Query Designer
CTRL+SHIFT+Q
CTRL+SHIFT+Q
Close a menu or dialog box, canceling the action
ESC
ESC
Sunday, January 3, 2010
MSSQL Servers - Truncating Log Files and Recovering Space
A common issue for users of SQL Server databases is disk space and the size of the physical log file and database. While we’re not going to attempt to make “one size fits all” statement on database maintenance plans, we though it would be helpful to provide a few suggestions that will help you trim the size of your files when you are in a pinch.
Steps to truncating log files and shrinking your database:
1. Get the physical names of your database file (MDF) and log file (LDF):
Run the following system stored procedure:
use
exec sp_helpfile
Record the name of the file from the “filename” colunm, excluding the path and file extension (e.g. if filename contains “C:\sqldatabases\yourdatabase_data.mdf” you want to save the string “yourdatabase_data”)
2. Truncate the database and shrink the database
The following set of SQL will shrink your database and “truncate” the log file. File in the parmaters surrounded by <…>. Note that you’ll need the two filename values from step 1.
USE
GO
BACKUP LOGWITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (, 1)
GO
DBCC SHRINKFILE (, 1)
GO
exec sp_helpfile
When complete, this script will output the same information as in step 1. Compare the new size with the old.
Steps to truncating log files and shrinking your database:
1. Get the physical names of your database file (MDF) and log file (LDF):
Run the following system stored procedure:
use
exec sp_helpfile
Record the name of the file from the “filename” colunm, excluding the path and file extension (e.g. if filename contains “C:\sqldatabases\yourdatabase_data.mdf” you want to save the string “yourdatabase_data”)
2. Truncate the database and shrink the database
The following set of SQL will shrink your database and “truncate” the log file. File in the parmaters surrounded by <…>. Note that you’ll need the two filename values from step 1.
USE
GO
BACKUP LOG
GO
DBCC SHRINKFILE (
GO
DBCC SHRINKFILE (
GO
exec sp_helpfile
When complete, this script will output the same information as in step 1. Compare the new size with the old.
Subscribe to:
Posts (Atom)
