Proprietary data in SQL2005

I'm trying to understand what I can do to protect proprietary data in SQL
2005. I have an application that currently uses Paradox and I plan to move
it to SQL 2005. Most users will start off with SQL 2005 Express, but will
eventually move to a 'full' version of SQL Server. Paradox allows me to
encrypt whole tables. I know it's not very good security because someone
can still do memory dumps etc. but in combination with licensing agreements
it's probably sufficient in my case to protect proprietary data stored in
the database. Can I achieve something similar in SQL 2005?
I see that I can encrypt data in specific columns, but I'm guessing that
those columns can't be indexed? (Or if they were indexed, it would have to
be the encrypted values rather than the original unencrypted values that
would actually be indexed thus making the indexing less useful)? It doesn't
seem like there's any way to encrypt a whole table?
In some of the stuff I've read, I get the sense that if I create a named
instance of a new SQL Server (Standard or Express), I can set up my instance
to only use SQL Authentication. Then I can prevent the Computer Admin of
the machine where SQL Server is installed from using his/her Windows
Authentication to access the database or named instance of the server. The
only way to configure the server or it's databases would be to know the SA
password and use SQL Authentication to log into the SQL server instance. Is
this correct? And, what does this gain me? How hard would it be to take
the database from my named instance and move it to a different SQL Server
Instance and then gain access to it?Hi,
Thanks for using Microsoft Online Managed Newsgroup.
From your description, I understand that:
You wanted to know:
1. if you can encrypt a whole table in SQL Server 2005;
2. if you can set up your SQL Server instance only use SQL Authentication;
3. how you can move your database from your named instance to a different
SQL Server instance and gain access to it.
If I have misunderstood, please let me know.
For your first question, by now there has been no such setting to encrypt a
whole table in SQL Server. You can encrypt a particular column in a table
by using a key or a certificate. You may refer to:
Improving Data Security by Using SQL Server 2005
http://www.microsoft.com/technet/it...tsec.mspx#EYAAC
For your second question, I would like to let you know that SQL Server has
only two authentication mode: one is Windows Authentication mode; the other
is Mixed Authentication mode (include Windows Authentication and SQL
Authentication). So Windows authentication will be always used by SQL
Server. Any trusted connections or local users can access your SQL Server,
however they will not have permissions to access databases if they are not
members of local administrators group and if you do not assign permissions
to them.
For your last question, I recommend that you:
1. Fully backup all of your user databases and logs;
2. Restore the databases to your new SQL Server instance;
3. Transfer SQL Server logins and passwords to the new SQL Server instance.
Please refer to:
How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/kb/246133/en-us
Also, I strongly recommend that you refer to this article for more
information:
How to move databases between computers that are running SQL Server
http://support.microsoft.com/kb/314546/en-us
Besides, for SQL Server 2005 instance, you can also use Copy Database to
move the databases:
Using the Copy Database Wizard
http://msdn2.microsoft.com/en-us/library/ms188664.aspx
If you are very concerned with the table level encryption, I recommend that
you give Microsoft feedback via the link:
https://connect.microsoft.com/SQL
Your feedback will be routed to SQL team so that this feature will probably
be included in the next release.
Look forward to your reply. If you have any other questions or concerns,
please feel free to let me know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi,
What is everything going on? Please feel free to let me know if you need
further assistance.
Have a great day!
Sincerely yours,
Charles Wang
Microsoft Online Community Support

Proper table structure and SQL Server limits

Hello all,
I am migrating a Paradox application to SQL Server. My problem is that the
existing Paradox table structure is limited. Correcting it will mean a
re-write of the application (Delphi app). Although the record and column
limits are higher in SQL Server, it will eventually hit the wall. So I was
wondering if anyone could offer advise on how we might do this best...
The application is essentially an evaluation tool. It is very generic and
can evaluate anything from buying a digital camera or laptop, to purchasing
a house. All the attributes of whatever it is being measure (the
attributes) are defined as a new column.
Example: let's say you are evaluating laptops. The core table may look like
this:
Fields:
1) Identifier
2) Processor Speed
3) Processor Cache
4) Hard Disk Size
5) LAN connection
6) Cost
So in this table, there are 5 things being measure, 6 columns in total. A
record might look like this:
Field Data:
Identifier | Speed | Cache | HD Size | LAN | Cost ...
HP | 3ghz | 2mb | 60G | Wireless | $2000
So you can see that this structure will hit the wall eventually as more and
more criteria are added. With Paradox, the max number of columns is 255. In
SQL Server this is 1024. Of course there is also a maximum record size. For
SQL Server this is 8060 bytes.
What I'd prefer is a structure that has no limits on how big it can be. In
short, I need a new table structure that is long and skinny, vs very wide
(or fat):
Fields:
Identifier
Attribute
Value
So using the above example, the data might look like:
Field Data
Identifier | Attribute | Value
HP | Speed | 3ghz
HP | Cache | 2mb
HP | HD Size | 60G
HP | LAN | Wireless
HP | Cost | $2000
Obviously this structure is preferred because now I won't hit any column or
record limits, but it makes just about everything else in the application
more difficult. Fundamentally data validation becomes difficult
In the above example, how would one define this structure?
Identifier varchar(50)
Attribute varchar(50)
Value ' <-- here lies my problem!!!
In some cases "value" would be an integer, sometimes numeric, sometimes
text. So then I figured, well I could have a number of tables as above,
each for the various data types, integers, numbers, free form text, etc.
Example:
For Integer data
Identifier varchar(50)
Attribute varchar(50)
Value integer
For floating point data
Identifier varchar(50)
Attribute varchar(50)
Value numeric
For free-form data
Identifier varchar(50)
Attribute varchar(50)
Value varchar or ntext
So now I have taken what was in one table, and split it across 3 or more
tables. Of course, all this data would need to be "re-connected" in a grid
like fashion so it looks like just one row of data to the client again.
It all seems to be getting very messy - to simply overcome a column/row
size limitation. Surely there must be an eloquent way to do something like
this?
thanx very much for any input.
regards,
-randall sell"Randall Sell" <randall@.bytewise.nospam.com.au> wrote in message
news:lsjYc.12329$D7.11613@.news-server.bigpond.net.au...
> Hello all,
> I am migrating a Paradox application to SQL Server. My problem is that the
> existing Paradox table structure is limited. Correcting it will mean a
> re-write of the application (Delphi app). Although the record and column
> limits are higher in SQL Server, it will eventually hit the wall. So I was
> wondering if anyone could offer advise on how we might do this best...
>
. . .

> So now I have taken what was in one table, and split it across 3 or more
> tables. Of course, all this data would need to be "re-connected" in a grid
> like fashion so it looks like just one row of data to the client again.
> It all seems to be getting very messy - to simply overcome a column/row
> size limitation. Surely there must be an eloquent way to do something like
> this?
>
You have pretty much worked through he issues here. And you're right: the
free-form attribute storage is a pain to manage, has no data integrity and
performs terribly. I'll just add that it's also not what relational
databases are _for_. An RDBMS is designed to model data using relations. A
relation is a tuple of related values, like
( HP, 3ghz, 2mb, 60G, Wireless, $2000 )
So the original design is the correct one. Mainly for performance reasons,
SqlServer puts a limit on the size of rows (in bytes and in number of
columns). If you really run out of room, you can always just create a
second table, and a third, etc which have a one-to-one relationship with
your original table.
David|||Now why didn't I think of that? That is the sort of input I was looking
for. It would also have some problems, such as creating dynamic queries and
not knowing which table contains a field, but the meta data should sort
that out, so it should be manageable.
thanx
-randall
David Browne wrote:
> So the original design is the correct one. Mainly for performance reasons
,
> SqlServer puts a limit on the size of rows (in bytes and in number of
> columns). If you really run out of room, you can always just create a
> second table, and a third, etc which have a one-to-one relationship with
> your original table.
> David
>

Proper table structure and SQL Server limits

Hello all,
I am migrating a Paradox application to SQL Server. My problem is that the
existing Paradox table structure is limited. Correcting it will mean a
re-write of the application (Delphi app). Although the record and column
limits are higher in SQL Server, it will eventually hit the wall. So I was
wondering if anyone could offer advise on how we might do this best...
The application is essentially an evaluation tool. It is very generic and
can evaluate anything from buying a digital camera or laptop, to purchasing
a house. All the attributes of whatever it is being measure (the
attributes) are defined as a new column.
Example: let's say you are evaluating laptops. The core table may look like
this:
Fields:
1) Identifier
2) Processor Speed
3) Processor Cache
4) Hard Disk Size
5) LAN connection
6) Cost
So in this table, there are 5 things being measure, 6 columns in total. A
record might look like this:
Field Data:
Identifier | Speed | Cache | HD Size | LAN | Cost ...
HP | 3ghz | 2mb | 60G | Wireless | $2000
So you can see that this structure will hit the wall eventually as more and
more criteria are added. With Paradox, the max number of columns is 255. In
SQL Server this is 1024. Of course there is also a maximum record size. For
SQL Server this is 8060 bytes.
What I'd prefer is a structure that has no limits on how big it can be. In
short, I need a new table structure that is long and skinny, vs very wide
(or fat):
Fields:
Identifier
Attribute
Value
So using the above example, the data might look like:
Field Data
Identifier | Attribute | Value
HP | Speed | 3ghz
HP | Cache | 2mb
HP | HD Size | 60G
HP | LAN | Wireless
HP | Cost | $2000
Obviously this structure is preferred because now I won't hit any column or
record limits, but it makes just about everything else in the application
more difficult. Fundamentally data validation becomes difficult
In the above example, how would one define this structure?
Identifier varchar(50)
Attribute varchar(50)
Value ' <-- here lies my problem!!!
In some cases "value" would be an integer, sometimes numeric, sometimes
text. So then I figured, well I could have a number of tables as above,
each for the various data types, integers, numbers, free form text, etc.
Example:
For Integer data
Identifier varchar(50)
Attribute varchar(50)
Value integer
For floating point data
Identifier varchar(50)
Attribute varchar(50)
Value numeric
For free-form data
Identifier varchar(50)
Attribute varchar(50)
Value varchar or ntext
So now I have taken what was in one table, and split it across 3 or more
tables. Of course, all this data would need to be "re-connected" in a grid
like fashion so it looks like just one row of data to the client again.
It all seems to be getting very messy - to simply overcome a column/row
size limitation. Surely there must be an eloquent way to do something like
this?
thanx very much for any input.
regards,
-randall sell"Randall Sell" <randall@.bytewise.nospam.com.au> wrote in message
news:lsjYc.12329$D7.11613@.news-server.bigpond.net.au...
> Hello all,
> I am migrating a Paradox application to SQL Server. My problem is that the
> existing Paradox table structure is limited. Correcting it will mean a
> re-write of the application (Delphi app). Although the record and column
> limits are higher in SQL Server, it will eventually hit the wall. So I was
> wondering if anyone could offer advise on how we might do this best...
>
. . .
> So now I have taken what was in one table, and split it across 3 or more
> tables. Of course, all this data would need to be "re-connected" in a grid
> like fashion so it looks like just one row of data to the client again.
> It all seems to be getting very messy - to simply overcome a column/row
> size limitation. Surely there must be an eloquent way to do something like
> this?
>
You have pretty much worked through he issues here. And you're right: the
free-form attribute storage is a pain to manage, has no data integrity and
performs terribly. I'll just add that it's also not what relational
databases are _for_. An RDBMS is designed to model data using relations. A
relation is a tuple of related values, like
( HP, 3ghz, 2mb, 60G, Wireless, $2000 )
So the original design is the correct one. Mainly for performance reasons,
SqlServer puts a limit on the size of rows (in bytes and in number of
columns). If you really run out of room, you can always just create a
second table, and a third, etc which have a one-to-one relationship with
your original table.
David|||Now why didn't I think of that? That is the sort of input I was looking
for. It would also have some problems, such as creating dynamic queries and
not knowing which table contains a field, but the meta data should sort
that out, so it should be manageable.
thanx
-randall
David Browne wrote:
> So the original design is the correct one. Mainly for performance reasons,
> SqlServer puts a limit on the size of rows (in bytes and in number of
> columns). If you really run out of room, you can always just create a
> second table, and a third, etc which have a one-to-one relationship with
> your original table.
> David
>sql

Proper table structure and SQL Server limits

Hello all,
I am migrating a Paradox application to SQL Server. My problem is that the
existing Paradox table structure is limited. Correcting it will mean a
re-write of the application (Delphi app). Although the record and column
limits are higher in SQL Server, it will eventually hit the wall. So I was
wondering if anyone could offer advise on how we might do this best...
The application is essentially an evaluation tool. It is very generic and
can evaluate anything from buying a digital camera or laptop, to purchasing
a house. All the attributes of whatever it is being measure (the
attributes) are defined as a new column.
Example: let's say you are evaluating laptops. The core table may look like
this:
Fields:
1) Identifier
2) Processor Speed
3) Processor Cache
4) Hard Disk Size
5) LAN connection
6) Cost
So in this table, there are 5 things being measure, 6 columns in total. A
record might look like this:
Field Data:
Identifier | Speed | Cache | HD Size | LAN | Cost ...
HP | 3ghz | 2mb | 60G | Wireless | $2000
So you can see that this structure will hit the wall eventually as more and
more criteria are added. With Paradox, the max number of columns is 255. In
SQL Server this is 1024. Of course there is also a maximum record size. For
SQL Server this is 8060 bytes.
What I'd prefer is a structure that has no limits on how big it can be. In
short, I need a new table structure that is long and skinny, vs very wide
(or fat):
Fields:
Identifier
Attribute
Value
So using the above example, the data might look like:
Field Data
Identifier | Attribute | Value
HP | Speed | 3ghz
HP | Cache | 2mb
HP | HD Size | 60G
HP | LAN | Wireless
HP | Cost | $2000
Obviously this structure is preferred because now I won't hit any column or
record limits, but it makes just about everything else in the application
more difficult. Fundamentally data validation becomes difficult
In the above example, how would one define this structure?
Identifier varchar(50)
Attribute varchar(50)
Value ? <-- here lies my problem!!!
In some cases "value" would be an integer, sometimes numeric, sometimes
text. So then I figured, well I could have a number of tables as above,
each for the various data types, integers, numbers, free form text, etc.
Example:
For Integer data
Identifier varchar(50)
Attribute varchar(50)
Value integer
For floating point data
Identifier varchar(50)
Attribute varchar(50)
Value numeric
For free-form data
Identifier varchar(50)
Attribute varchar(50)
Value varchar or ntext
So now I have taken what was in one table, and split it across 3 or more
tables. Of course, all this data would need to be "re-connected" in a grid
like fashion so it looks like just one row of data to the client again.
It all seems to be getting very messy - to simply overcome a column/row
size limitation. Surely there must be an eloquent way to do something like
this?
thanx very much for any input.
regards,
-randall sell
"Randall Sell" <randall@.bytewise.nospam.com.au> wrote in message
news:lsjYc.12329$D7.11613@.news-server.bigpond.net.au...
> Hello all,
> I am migrating a Paradox application to SQL Server. My problem is that the
> existing Paradox table structure is limited. Correcting it will mean a
> re-write of the application (Delphi app). Although the record and column
> limits are higher in SQL Server, it will eventually hit the wall. So I was
> wondering if anyone could offer advise on how we might do this best...
>
.. . .

> So now I have taken what was in one table, and split it across 3 or more
> tables. Of course, all this data would need to be "re-connected" in a grid
> like fashion so it looks like just one row of data to the client again.
> It all seems to be getting very messy - to simply overcome a column/row
> size limitation. Surely there must be an eloquent way to do something like
> this?
>
You have pretty much worked through he issues here. And you're right: the
free-form attribute storage is a pain to manage, has no data integrity and
performs terribly. I'll just add that it's also not what relational
databases are _for_. An RDBMS is designed to model data using relations. A
relation is a tuple of related values, like
( HP, 3ghz, 2mb, 60G, Wireless, $2000 )
So the original design is the correct one. Mainly for performance reasons,
SqlServer puts a limit on the size of rows (in bytes and in number of
columns). If you really run out of room, you can always just create a
second table, and a third, etc which have a one-to-one relationship with
your original table.
David
|||Now why didn't I think of that? That is the sort of input I was looking
for. It would also have some problems, such as creating dynamic queries and
not knowing which table contains a field, but the meta data should sort
that out, so it should be manageable.
thanx
-randall
David Browne wrote:
> So the original design is the correct one. Mainly for performance reasons,
> SqlServer puts a limit on the size of rows (in bytes and in number of
> columns). If you really run out of room, you can always just create a
> second table, and a third, etc which have a one-to-one relationship with
> your original table.
> David
>