SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

The Voice of the DBA

Hack Resistant?

Security should be on the forefront of every data professionals' mind. It doesn't matter if you are developer, administrator, or other position, you ought to be considering the security implications of changes you make to the database. More and more data is being stored in databases, and often it's moved between databases as well. Whether that's to data warehouses, or development environments, we ought to be considering security to be more a part of our daily work and process rather than something limited to specific systems.

Vendors are trying to make systems more secure. I see the encryption capabilities grow with each version of SQL Server, and new features have been added, like TDE, to help technology professionals secure their data. Other vendors have introduced other safety mechanisms, and one caught my eye by claiming to be "hack resistant". It's the ZenithVault database says it is practically impossible for hackers to gain access to storage systems and steal confidential information. It supposedly does this by "data splitting", moving parts of data onto separate servers.

It sounds like a good solution, in the same way that TDE is a good solution. If someone is able to access your physical files, they won't be able to read the data in them. That's a good precaution, and it works well in TDE's case (I can't speak for ZenithVault), but it's also not hack resistant.

The biggest hacking problem that I see in the world today is SQL Injection. Since legitimate accounts can access your database, often through web-based front ends, and SQL Injection uses these same accounts to access data, none of the encryption and security capabilities you set up protect you. None of these items come into play when you have code that allows hackers to inject their own commands through your existing application, web based or not.

Secure coding is important, and it's something that we should all require and practice as we build software. Your application might not access secure data today, but that might change in the future.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums


The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com with  comments definitely appreciated. Overall RSS Feeds:  

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

 You can also follow Steve Jones on Twitter and find links and database related items and announcements.

ADVERTISEMENT
deploymentmanager

Deployment Manager 2 is now free!

The new version includes tons of new features and we've launched a completely free Starter Edition! Get Deployment Manager here.

SQL Backup

New! SQL Backup Pro 7.4

Try out the latest version today for faster, smaller compressed backups on all editions of SQL Server. Download a free trial now.

SQL Source Control

Get your SQL Server database under version control now!

Version control is standard for applications, but databases haven’t caught up. So how can you bring database development up to speed? Why should you start? Find out…

Featured Contents

 

Orphaned Distribution Transactions

Shashank Srivastava from SQLServerCentral.com

Learn about orphaned distribution transactions in SQL Server and how you can clear them. More »


 

SQL Server Central Webinar Series #24: Prepare for When Disaster Strikes

Press Release from SQLServerCentral.com

Our next webinar on July 23rd has Steve Jones presenting on some disaster stories with ideas on how you might prevent, or mitigate the effects, of those problem situations. More »


 

SQL Saturday #231 Cocoa Beach

Press Release from SQLServerCentral.com

Join us for a free day of SQL Server training and networking on July 27 in Cocoa Beach, Florida. More »


 

Encrypting and Decrypting SQL Server Stored Procedures, Views and User-Defined Functions

Additional Articles from MSSQLTips.com

Some companies put business or application logic in SQL Server using stored procedures, views and functions to return values to the calling applications or perform tasks. This is not unusual in companies that use the SQL Server layer to perform business tasks, such as finance operations, or incorporate application functionality into the programmability layer. Here's a tip to preserve secrecy on some procedures, views or functions in order to maintain security. More »

Question of the Day

Today's Question (by Vivek Raj):

What is returned by select statement?

CREATE TABLE Test 
( Ident INT NOT NULL IDENTITY (1,1)
, varfield varchar(100)
);

INSERT INTO Test VALUES ('abc')

DBCC CHECKIDENT ('Test',RESEED,100)

truncate table Test

INSERT INTO Test VALUES ('def')

SELECT Ident FROM Test

drop table Test 

Think you know the answer? Click here, and find out if you are right.


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: T-SQL.

We'd love to give you credit for your own question and answer.
To submit a QOD, simply log in to the Contribution Center.

ADVERTISEMENT

SQL Server Execution Plans

SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon!

Yesterday's Question of the Day

Yesterday's Question (by pramod singla):

Create table test(a int)

insert into test values (null)
insert into test values (2)
insert into test values (3)
insert into test values (1)
insert into test values (null)

select *from test
order by a

What will be the order of null values?

Answer: In the begining of the result set

Explanation: In T-SQL the nulls are returned at the beginning of the result set if ordering is specified. Standard SQL supports the options NULLS FIRST and NULLS LAST to control how NULLs sort, but T-SQL doesn’t support this option.

Ref: http://msdn.microsoft.com/en-us/library/ms188385.aspx


» Discuss this question and answer on the forums

ADVERTISEMENT

SQL Server Execution Plans

SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon!

Featured Script

Long Running Jobs Monitor

Gregory Ferdinandsen from SQLServerCentral.com

This script should be executed from the SQL Server Agent on a recurring basis (I run it hourly).  The script will monitor jobs and alert for jobs that run over the specified threshold (Defaults to 24 hours).

By default, I have excluded the two Management Datawarehouse Jobs that strt running when the server is brought online/MDW starts to collect data.  You may exclude upto 5 other jobs that you know exceed the threshold.

More »

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

need solution and feedback to the below question!! - here is the sample data . create table empp(eno int,ename varchar(25),dept varchar(3)) insert into empp values(101,'raghava','hr') insert into empp values(102,'krish','hr') insert into empp values(103,'venkat','fin') create...

Management Studio 2012-Adding new Logins - Hi: I am new to SQL Server and am trying to create a new login ID and password with permissions. I...

Need to connect to sql instace using an intermediate sql server - Hi, I have a rare case need. I don't have direct access to one our clients sql server(cannot connect) I can...

Log Shipping Jobs on Secondary not getting created - Hi all: I'm trying to test my understanding of log shipping here. I'm currently trying to get it working on 2...

SQL Server 2005 : Backups

Odd permissions issue when backing up to a network location - I have a 2005 box and 3 2008 boxes. All 2008 boxes backup fine to the network location however, I'm...

SQL Server 2005 : Business Intelligence

Execute Child packages parallel in loop - hi , I have a master package which calls the child packages from a folder dynamically, when i tried this with...

How to change the connection string of child packages in Execute Package task? - Hi Friends, I am tryting to execute multiple packages so i am using Execute Package Task to run the packages...

Failure sending mail: The transport failed to connect to the server - Hi, Please any body Knows this Please Tell me Help Full me When i was trying to Create a Subscription . It was...

SQL Server 2005 : Development

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. - Hello All I m not able to find this error Subquery returned more than 1 value. This is not permitted...

Downgrade INSERT with OUTPUT to Sql Server 2000 - How do I convert the following Sql script so that it will run on Sql Server 2000 (which does not...

SQL Server 2005 : Working with Oracle

The OLE DB provider "OraOLEDB.Oracle" has not been registered. - Hello I would like to connect oracle database to sql server 2005 and i installed oracle client and also used the...

SQL Server 2005 : SQL Server 2005 General Discussion

Replication as a CDC enabler? - We have a situation where we are required to track changes to data in a handful of tables but we...

Query Help V2 - I posted a question a few weeks ago, and due to a external influence i managed to confuse the whole...

SQL Server 2005 : SQL Server Express

How do I allow all logged in users to access a SQL Express 2008 R2 database? - I'm working on a new WPF application, which uses a SQL Server 2008 R2 Express database, stored locally on the...

SQL Server 2005 : SQL Server 2005 Integration Services

Argument "Server1" for option "connection" is not valid. The command line parameters are invalid. The step failed. - I have a SSIS package run fine in BID, but when run as a sql agent job, it failed at...

Import Multiple XML files into Sql Server using SSIS - Hello All... I have a set of XML files which I need to import into database on Regular basis(after validating some...

Overwriting Excel File with SSIS package - Hi All, I have an SSIS package which exports the data to an Excel file using Excel Destination component. Could any...

SQL Server 2005 : T-SQL (SS2K5)

using isnull function in select query - select isnull(id,0)+1 as ID from TABLE1 i have the id field blank with defualt value as 0 but when i use...

inserting horizontal records from table1 as vertical records in table2 - i have table with horizontal records , which i need to insert in another table as vertical records example FDT1 TODT1 FDT2 TODT2...

SQL Server 2005 : SQL Server Newbies

Grouping by day on a rollover count - Hi, I have a table containing a count that rollsover at [u]around[/u] 32000 to 0 possibly many times a day and...

Barcodes Code128 generator function - I'm looking for a [b]barcode generating Function[/b] (in SQL 2005), that uses the standard [u]Code128[/u]. The result of calling the...

SQL Server 7,2000 : Administration

Boxes symbol in query output - Hi, Here is my friend's sql server version info : Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright...

SQL Server 7,2000 : T-SQL

Parse Full Name Into Last, First and Middle - I am trying to develop a custom function that when given a Full Name formatted as Lastname Suffix., Firstname Middlename...

SQL Server 2008 : SQL Server 2008 - General

Permissions - Domain Question - Bit of a strange one. We have a user who has a db_owner role but still wasnt able to perform...

Get date only and order by in correct way - I converted orderdate to get date only using code below. convert(varchar(20), orderdate, 101) The problem is that orderdate can not be...

Alerts for Database Mirroring - Hi I'm trying to get SQL Server to alert me via email when the Primary instance loses connection with Mirrored instance...

ERROR on SELECT * FROM OPENDATASOURCE using ('Microsoft.ACE.OLEDB.12.0) - Hello, can some one point me to proper direction here or it just stupid question (not sure what to think) I...

Performance issue on DB - Hi, We have a sql 2005 prod server,where one of the DB became very slow past 5 days. i have observer that...

SQLCMD seems to use a different code page - Odd one this. I use SQLCMD mode of SSMS extensively when managing major projects. This was I can get developers to...

Timeout exception - Hi, I am facing problem to connect with SQL Server 2008R2 using java application/tool It through timeout exception. But its working fine...

User datafeed overwrite with ssis package - Hi, I have tables user and userprofiles. I get datafeed every night in csv file. I need to create ssis package...

Handling error message - Hi, while writing script I have to check that whether input value is correct or not and then display the...

Audit connections via extended events - Hi, I am looking for a little bit of advice on how to setup an extended event in SQL 2008 R2...

Error on Restore - Hi, The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\sample.mdf' cannot be overwritten. It is being used by database 'sample'. File 'sample' cannot be...

Raid 10 - Hello. The average bytes/read is between 64k and 128k and the average bytes/write is between 32k and 64k. What is the best,...

sp_addextendedproperty - Hi All, I came across "sp_addextendedproperty" in Adventureworks2008R2 . EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key for ContactType records.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'ContactType', @level2type=N'COLUMN',@level2name=N'ContactTypeID' GO Can someone...

Restore the database into temp database - Hi, How can i restore the database into temp database in SQL server 2008 R2 Advance Thanks.......,

decalre select - create table kateqor (id int not null identity(1,1), Name nvarchar(50)) insert into kateqor (Name) values (N'M?nzild? qurum') select*from kateqor but when I do a query declare @kat nvarchar(50) set...

SQL Disk Error - Dear Gurus, I am getting attached error of disk when installing SECOND INSTANCE of SQL 2008 R2 please help . http://tinypic.com/view.php?pic=2nuk85z&s=5 http://tinypic.com/r/14crv5u/5 http://tinypic.com/r/t057jr/5 Your kind...

SQL Server Error Log retention - Hi Guys, Understand that by default SQL Server has 7 error logs. The oldest error log records will be deleted each time...

Clustered Index Ordering & Insert Speeds - Hi, We used to have a clustered index which consisted of 5 fields, this was ordered incorrectly which meant new inserts...

restoring databae to setup Mirroring - i have 123Gig of database, i am trying to setup mirroring for it. we take full backup every week and diff backup...

Restoring diff backup problem - i am trying to mirror the database, i have restored the full database backuop now i need to restore diff back and...

SQL 2000 - Maintenance Plan - In SQL 2000 while creating maintenance plan, there was Optimizations which REORGANIZED the data and indexes. What it does really?...

How to take backup of Single table and how to restore? - Hi... How to take backup of Single table and how to restore? is there any query like database backup? shivakumar...

handling errors in SSIS 2008 - Hi every one I'm working with SQLServer Integration Service 2008 I have some questions about handling fails in SSIS: I have a package...

SSIS load? - When setting up a new SQL Server 2008 R2 is there any advantage to installing SSIS on a separate server...

SSIS in cluster - I have two nodes nodeA and nodeB on a sql server on a fail over clustered environment. Each of these...

how i can identify local windows service that coresponds to sql spid? - Good morning Everyone, Our environment: sql server 2008 r2 sp2 on windows 2008 r2 enterprise sp1, 2 node active/passive cluster; 200...

Is this a crazy way to migrate from one OS version to another? - I need to migrate all the servers I support from Windows Server 2008, to 2008 R2 by the end of...

Filegroup - Hi All, Please let me know whether it is possible to use the same secondary filegroup for 2 different databases. I tried...

Insert bulk failed due to a schema change of the target table. - Hello Expert. First time I used sqlbulkcopy its performance is impressive, however my application threw a SQL exception after processed about...

Replication DB - Dead lock issue - Hi, We have One Database which is a Replication Db, sometimes during beginning of the month, lot of user activities going...

SQLSERVER Agent is not running- SQLSERVER 2008R2 - Hello: We have Windows 7 with SQLSERVER 2008R2 Express Edition 10.5 Installed. Database Engine and SQL Browser services are running perfect...

SQL 2008 Error: 4014, Severity: 20, State: 11 - Hi I have had a problem with our SQL server which has resulted in the error message below. I have searched...

SQL Server 2008 : T-SQL (SS2K8)

Performance Tuning Large update statement--HELP - I have a 1.2 TB database in which I am updating a couple columns across 11 tables. T1 (F_name, L_Name, Address) t2...

Forecasting Year Numbers - Hi all Apologies if this is posted in the wrong section but I didnt really know where it belonged. I have a...

Sum Negative Numbers - Hi All, I have a column GNLBalance with negative and positive balances. I want to add these numbers together when another...

Locking on update...inner join - Thought I would re-write as it didnt make sense to me! When an update query is based on a join, so...

Help needed for Select Query - [b]TableName:Customer[/b] --------------------------------------------------------------------------------- CustomerId CustomerName Location City District PinCode Status --------------------------------------------------------------------------------- 4 Pavan HitechCity Hyd WG 7687 1 5 Kalyan CinemaStreet Tuni EG 1234 1 7 MVRao LBNagar Kkd GG 123 0 8 MMRao Pathape

SQL Server 2008 : SQL Server Newbies

How to get the table name from a objectID? - Hi I am trying to find the name of a table in our database given a objectID, when i run this select...

SQL Server 2008 : SQL Server 2008 High Availability

Database Mirroring Or Log shipping?? - Hello all, I want to maintain another database only for Reports.what is the better ways for achieving this Database Mirroring...

Log Shipping or Transactional Replication - Hi, We have an ecommerce site for which primary infrastructure (DB and web servers) is based in Chicago. We have a...

SQL Server 2008 : SQL Server 2008 Administration

about system databases. - when system databases like model and msdb are in suspect mode,will sql server will be up and running or live? thanks...

hai all - if i have a single table which is of 1 TB in size and the fragmentation percent is 85% ,i...

how to find log shipped databases in given server - Hi all, I am really hard to finding how many databases are under logshipping in a given server. also that script...

Receive notifications that the Transaction Log Backup has failed when it has succeeded. - Receive notifications that the Transaction Log Backup has failed when it has succeeded. I have been receiving notifications that the Transaction...

SQL Server on Windows Server 2012 essentials - Admin Help - I have just installed Windows Server 2012 essentials on my HP Mediasmart server. I am going to be using server...

Hai all - can anyone please explain me "why there will be connection timeout errors in SQL server" any causes for that and...

Sql Server - Index ReBuild - I will be planning to do the Rebuild/ Reorg Indexes as my client never had done this and never run...

Upgrade from Sql 2003 to Sql 2008 - Hi, We are planning to upgrade from Sql 2003 to Sql 2008 and I would like to know what needs to...

copy system database files - Dear All, I would like to have a backup of the SQL system databases (the files themselves) not the SQL backup....

SQL job or task prioritization - Dear All, My current SQL configuration is SQL 2008 standard edition (Vmware virtualized 2 node failover cluster) on windows 2008 R2...

SQL Server 2008 R2 , change domain - SQL Server 2008 R2 Ho guys I need some help from you: I have a SQL Server 2008 R2 installation in a...

Restoring Backup - Hello, I would like to know that what will be the best way to restore from Full backup, Differential and Transaction...

Programming : General

Trouble with date conversion - I want to match the year field in a table and pull out all that are a year old. I'm...

SQLServerCentral.com : Anything that is NOT about SQL!

New kid on the block - I came over here when the NNTP bridge for the MSDN forums stopped working, and I've only been here for...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services

Reporting Services slow - My Reporting Services is getting slow to jump from report to report. Is there any way to improve your performance?...

Two instance of Reporting Service - Correct me if I'm wrong but it's not possible to have to instance of Reporting Service under one SQL instance....

Reporting Services : Reporting Services 2005 Development

Repeat Table - Hi, I have a table that has two header, one detail and one footer. The first header has title of the...

Data Warehousing : Integration Services

Issues in calling the same child package from multiple sources - Hi, I have a master package which will call the same child package with different variables. To avoid a variable assignment...

Mixed formatted Input Text Files - I'm new to SSIS packages so feel free to yell at me if I'm making this more difficult, and please...

Using a Script Task to Create a ADO.NET (ODBC) Data Flow Source - I am trying to create a SSIS Script Task in SQL 2008 R2 (64-bit) that creates a package. The code...

To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com