SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Script

The Voice of the DBA

Encrypting SQL Code

This editorial was originally published on April 10, 2009. It is being re-run as Steve is on vacation.

I recently engaged in a discussion with someone that was building an application on SQL Server. This person had a bunch of SQL code that was being put in stored procedures and then being sent to client sites. The developers were worried about clients modifying their code and wanted to send "secure updates" to the client by encrypting the stored procedures and giving the clients the encrypted text.

Apart from the hassles of getting this to work, I asked by would they bother. There are decryption routines available and this isn't meant to be a secure way to hide your code. Heck, even application code can be decompiled, and if they're likely to mess with the code, they likely have the skills to get the source.

So for this Friday's poll, I'm wondering about how you feel about encrypting code in SQL Server. I want to know what you think.

Is there a point?

Is there a reason to encrypt stored procedure code? After all, there are many, many vendors that sell applications built on SQL Server, with stored procedures.  Most of that code isn't encrypted and it's usually not a problem. Most customers don't mess with the code and there are usually prohibitions written into support agreements.

Personally I don't think there are many great ideas, and likely very, very few in the database space, that are worth securing. Someone doesn't buy a software package so the can learn how you wrote it. Most of them buy software because it solves a problem and saves them time. If you can deliver a well performing, and good looking application, no one cares about the code.

But I'm curious what the rest of you think, both end users and software developers. Is there really a good reason to worry about encrypting your code?

Steve Jones


The Voice of the DBA Podcasts

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

You can also follow Steve Jones on Twitter:

Overall RSS Feed: or now on iTunes!

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.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

Steve Jones from SQLServerCentral.com

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

ADVERTISEMENT
SQL DBA Bundle

The seven tools in the SQL DBA Bundle support your core SQL Server database administration tasks.

Make backups a breeze! Enjoy trouble-free troubleshooting! Make the most of monitoring! Download a free trial now.

SQL Prompt

5 ways to code effortlessly

Discover the different ways you can make writing, exploring, and refactoring SQL code refreshingly effortless with SQL Prompt 6. Download a free trial.

SQL Monitor

Check SQL Server performance at a glance

We consulted 1000 SQL Server professionals to make SQL Monitor’s UI as clear as possible. Start monitoring with a free trial.

Featured Contents

 

Database Restore Automation

Paul Brewer from SQLServerCentral.com

Read about Paul Brewer's Framework (Version 2) for Database Restore Automation. More »


 

Free eBook: 45 Database Performance Tips for Developers

Press Release from Red-Gate

As a developer, if you need to go into the database and write queries, design tables, or determine the configuration of your SQL Server Systems, these tips should help make sure you're not unnecessarily sacrificing database performance. This eBook has 45 easy tips to improve the performance of your indexes and T-SQL queries, and hunt down problems within ORM tools and database design. More »


 

Fundamentals of Vendor Management

Additional Articles from SimpleTalk

Creating and maintaining mutually beneficial relationships with external vendors is one of the pillars of good project management. Dwain Camps goes through what to expect and allow in your client-vendor relationship during the various stages of a given project to ensure its success and secure that all important win-win outcome. More »


 

From the SQLServerCentral Blogs - A Rickety Stairway to SQL Server Data Mining, Part 14.5: The Predict Method

SQLServerSteve from SQLServerCentral Blogs

By Steve Bolton …………In order to divide the Herculean task of describing custom algorithms into bite-sized chunks, I omitted discussion of... More »

Question of the Day

Today's Question (by Steve Jacobs):

What type of join is produced when running the SQL Script below?


SELECT city
    FROM contacts
UNION
SELECT city
    FROM contacts2
    GROUP BY city
    ORDER BY 1

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

Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

Ace your preparation for Microsoft® Certification Exam 70-461 with this 2-in-1 Training Kit from Microsoft Press®. Work at your own pace through a series of lessons and practical exercises, and then assess your skills with practice tests on CD—featuring multiple, customizable testing options.

Maximize your performance on the exam by learning how to:

  • Create database objects
  • Work with data
  • Modify data
  • Troubleshoot and optimize queries

You also get an exam discount voucher—making this book an exceptional value and a great career investment.

Yesterday's Question of the Day

Yesterday's Question (by Chirag Patel):

Examine this code:


CREATE TABLE table1
    (
      firstname VARCHAR(50)
    , lastname VARCHAR(50)
    );
CREATE TABLE table2
    (
      address1 VARCHAR(50)
    , address2 VARCHAR(50)
    , zip VARCHAR(50)
    , name VARCHAR(50)
    );
INSERT INTO table1
    VALUES  ( 'f1', 'l1' ),
            ( 'f2', 'l2' ),
            ( 'f3', 'l3' ),
            ( 'f4', 'l4' ),
            ( 'f5', 'l5' ),
            ( 'f6', 'l6' ),
            ( 'f7', 'l7' );
INSERT INTO table2
    VALUES  ( 'add1', 'add2', '12300-12', 'f2' ),
            ( 'add3', 'add4', '12300-14', 'f4' ),
            ( 'add5', 'add6', '12300-20', 'f1' ),
            ( 'add7', 'add8', '12330', NULL );
If we run this query, which names will be returned from table1?

SELECT t1.firstname
      , t1.lastname
    FROM table1 AS t1
    WHERE t1.firstname NOT IN ( SELECT t2.name
                                    FROM table2 AS t2 );
DROP TABLE table1;
DROP TABLE table2;

Answer: 0

Explanation:

While executing the query, SQL uses three-valued logic, driven by the existence of NULL, which is not a value but a marker to indicate missing (or UNKNOWN) information. When the NOToperator is applied to the list of values from the subquery, in the IN predicate it looks somewhat like this.

t1.firstname NOT IN ('f2','f4', 'f1', NULL)

The expression "name = NULL" evaluates to UNKNOWN. According to the Three valued logic there could be any thing indicating (True, False, some indeterminate third value ).

In the present scenario we have "name = NULL" which leads to UNKNOWN as indeterminate value. Evaluating t1.firstname NOT IN ('f2','f4', 'f1', UNKNOWN) gives all rows filtered out and returns empty result set.

Excellent articles on this topic are: 


» Discuss this question and answer on the forums

Featured Script

Security Queries: Database-level

Greg Drake from SQLServerCentral.com

This script contains various queries I have written on the subject of security as it applies to a specific database. It's arranged so I can either (A) run one query I need alone or (B) run the whole thing as a batch to dump out everything at once.

Option B might not seem useful at first (information overload!), but consider that if your output in SSMS is set to "Results to Text", this will allow you to quickly copy off a single text file that contains everything you might want to know about that database's security at a point in time. Furthermore, the queries are carefully structured and ordered to be consistent. This makes two of these text files easy to compare with a standard compare/diff/merge application (e.g. WinMerge, Beyond Compare).

Included queries:

  • Database roles
  • Database users
  • Database role membership
  • Database role and user permissions
  • Database-level SQL/Assembly module EXECUTE AS
  • Schema ownership
  • Individual ownership of database objects if not owned by schema
  • Database users linked to server login
  • Database users that were linked to server login, but are not anymore

The last query is a re-write of the 'report' mode of sp_change_users_login.

Please also see related script "Security Queries: Server-level".

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 2012 : SQL 2012 - General

Problems connecting to SSMS 2012 from VS 2010 - This application used to work. Its been a year and a half since I used it. The error I'm getting...

Script required to restore Database - Hi, I need to restore a database. My database consists of 7345 data files. I got a request to restore the...

SQL Server 2012 Cluster failover question - I have a SQL Server 2012 Cluster running on our network which spans between two physically separate buildings via fiber....

Data loading best practices for 5-10 millions of rows. - Hi Expert, As this a huge environment wanted to impement best practice for loading 5 - 10 millions of rows, please advice. How...


SQL Server 2012 : SQL Server 2012 - T-SQL

Return the last version of set of records - Hello all, I have a query I am working on where an orders table has a version column for each...


SQL Server 2008 : SQL Server 2008 - General

Downgrading Compatibility Level - I recently had to take a snapshot for replication of a client database, this failed because the database contained an...

Importing > 4000 text characters! - I have a simple text file which has more than 4000 characters in one column. With the Import feature (Locale...

Create a Table From a Query - I would like some subsets (tables) from may main table. dumb q: How can i quickly create a new table from...

how to create a loop??? - fairly new with advanced queries and need help.. how can I make the statement below automatic (loop) so I do...

What is the most important thing to the DBA , is to know windows server or .net? - What is the most important thing to the [b]DBA [/b], is to know [b]windows server[/b] deeply or to know [b].Net...

REPLACE W/ WILDCARD%? - Hello World, Just curious... I have a text column w/ text like: Frog12texttext Frog13textetc Frog15etctextext Is there a way to REPLACE just the Frog12, Frog13,...

Help with Temp Table with Case Statement - I'm trying to build a temp table to run my Start Date and End Date Parameters from the current fiscal...

Reporting Service error The server content appears to be out of date. - [b]visual studio 2008 create project Business Intelligence Projects --> Analysis Services Project[/b] While click rebuild first and right click process...

Questions regarding Spatial Data performance - Hi all, I was wondering if anyone could chime in with random tips or thoughts on what I'm doing. So I've...

Rolling 3 month average cost help - Hi guys, I need some help calculating a 3 month avergae rolling cost. I have been trying to figure this out...


SQL Server 2008 : T-SQL (SS2K8)

t-sql 2008 r2 concatenate - In an sql server 2008 r2 database, I have one column that I need to separate the values. I then...

Regarding storedprocedure - I have written a stored procedure like USE [nxnv1_temp] GO /****** Object: StoredProcedure [dbo].[SP_CONSULTATION_DETAILS1] Script Date: 11/02/2013 10:06:41 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER...

Pivot, Unpivot and Cross Apply !!!! Nothing seems to work - Consider the below T-SQL [code="sql"] CREATE TABLE Temp10(Source nvarchar(50),Target nvarchar(50),Property1 int,Property2 int, Property3 int,Property4 decimal,Property5 nvarchar(100),Property6 nvarchar(50)) INSERT INTO Temp10 values('A','DEF',10,8,20,12.34,'Good','Bad') INSERT INTO Temp10 values('A','GKL',2,14,0,20.4,'Bad','Good') INSERT...

Undo the cross tab - I'd like to put the data in table #Things into a normalized structure. Table #NewThings represents my desired output. I...


SQL Server 2008 : SQL Server 2008 High Availability

Issue with Logshipping - Can someone help me on this.. For testing,I've configured logshpping on same server (primary and secondary are in same instance).. LSCOPY job...

Logshipping to another server with different drive names - Hello, We have primary with drive F,L and secondary server with drives D,L. Data files from F goes to D in secondary...

iSCSI network binding order - While this is not really SQL specific, does anyone have any information on where in the binding order the iSCSI...


SQL Server 2008 : SQL Server 2008 Administration

Can someone translate this restore error? - I have a 100GB backup to test restore. After 4+ hours and 90% done, I received the below error: [quote] Restore failed...


SQL Server 2005 : SQL Server Newbies

Three tables SQL query - I have a table (Vehicles) which contains a list of vehicles. VehicleID PlateNo CurrentDriver I also have a table (History) which contains a the...


Reporting Services : Reporting Services

Parameter Boolean - Hello Friends, new time come on a question. Thanks to new time I have responded. The following parameters related to ask is,...

Date range defaults - Hello and happy Friday! I have a several reports that accept date range parameters. These are not the textboxes that show...


Reporting Services : Reporting Services 2005 Administration

SSRS history - The reports being run by SSRS are not being logged in the ExecutionLog table correctly any more. It used to...

Determine who deleted a Report - SQL Server 2005 Reporting Services - Hi All, Working on SQL Server 2008 Reporting services. Is there a way to determine who deleted a report. The log...


Reporting Services : Reporting Services 2008 Development

ssrs 2008 r2 concateante values into one cell - In an sql server 2008 r2 report, I have one textbox that I need to concantenate values together. I then...

SSRS 2008 r2 concatenate - In an sql server 2008 r2 database, I have one column that I need to separate the values. I then...

ssrs 2008 r2 export to excel - I have an SSRS 2008 R2 report, where I have users that want to export the reports to excel. They...

SSRS 2008 substring process - In an SSRS 2008 existing report, I have one column that I need to separate the values. I am not...


Data Warehousing : Integration Services

SSIS on SQL2008 - Just want to get some ideas on how to set up and deploy SSIS packages (examples will be great) in...


SQL Server 7,2000 : T-SQL

Help comparing Sum to Total amt - I need a little help. I have an invoice table and and invoice detail table. I pulled the data I...


Microsoft Access : Microsoft Access

How to avoid opening a password-protected mdb - I'm looping through my pc's folders to open each mdb that I encounter and look for a sub in its...

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