Database Id prb !

Hi,
I was profiling the Audit Schema Object Access Event in SQL Server 2005,
and when I executed the system stored procedure "sys.xp_msver" I found that
the database id of the this procedure is shown as "32767" which doesn't
exists in the sys.databases view ? Any idea to what database it belongs to ?
Does the above belong to the "mssqlsystemresource" or "distmdl"
databases ?
I also wanted information as to how do I query the system tables, it
gives me an error saying the object doesnt exists
Experts pls help
TIA
Thanks
P[1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped
to
any database.
- Breaking changes to database engine features in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms143179.aspx
[2] To query a system table, you will need to prefix the table with the
appropriate database that you are going to query. E.g.,
SELECT * FROM msdb..backupfile
- Querying the SQL Server System Catalog
http://msdn2.microsoft.com/en-us/library/ms189082.aspx
- Querying the SQL Server System Catalog FAQ
http://msdn2.microsoft.com/en-us/library/ms345522.aspx
Martin Poon
Microsoft MVP - SQL Server
----
- SQL Server 2005 books online
http://www.microsoft.com/technet/pr...oads/books.mspx
- SQL Server 2000 books online
http://www.microsoft.com/sql/prodin...ions/books.mspx
========================================
==========
"Prasad" <ekke_nikhil@.yahoo.co.uk> bl
news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl g...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server
2005,
> and when I executed the system stored procedure "sys.xp_msver" I found
that
> the database id of the this procedure is shown as "32767" which doesn't
> exists in the sys.databases view ? Any idea to what database it belongs to
?
> Does the above belong to the "mssqlsystemresource" or "distmdl"
> databases ?
> I also wanted information as to how do I query the system tables, it
> gives me an error saying the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Yes, sys.xp_msver lives in the system resource database. You can see this by
connecting through the
dedicated administrator connection (DAC), looking at the source code from th
e view sys.databases and
then do a select directly against the physical system table that the view sy
s.databases uses.
I don't know what you want to achieve in the end, but best bet is probably t
o disregard objects that
lives in the database with id 32767.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.g
bl...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server 2005
, and when I executed
> the system stored procedure "sys.xp_msver" I found that the database id of
the this procedure is
> shown as "32767" which doesn't exists in the sys.databases view ? Any idea
to what database it
> belongs to ?
> Does the above belong to the "mssqlsystemresource" or "distmdl" databas
es ?
> I also wanted information as to how do I query the system tables, it giv
es me an error saying
> the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Hi,
Thnx for your information, but I have a question, this must be a naive
for you.
But if I query from a database "db" context shouldnt the profiler show
that the database name is "db" and database id is the id the database "db" ?
Same thing for the tables also ?
The sys.all_objects lists that these are system tables and system stored
procedures but then why it is not showing that it comes from this database
in the profiler?
I want to say to the profiler that I want to monitor all the "Audit
Schema Object Access Event" in this particular database
But then since these SP's and tables have the database id different then
what is configured it is ignored even when these SP's are fired in the
database context of what is configured
Any suggestions ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mappe
d to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with th
e
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/pr...oads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodin...ions/books.mspx
> ========================================
==========
> "Prasad" <ekke_nikhil@.yahoo.co.uk> bl
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl g...
> 2005,
> that
> ?
>|||Hi,
Regarding your suggestion for the system table I tried the same thing as
told by you "select * from msdb..backupfile"
But in the profiler it still returns that as a User table instead of a
System table.
Any ideas ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mappe
d to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with th
e
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/pr...oads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodin...ions/books.mspx
> ========================================
==========
> "Prasad" <ekke_nikhil@.yahoo.co.uk> bl
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl g...
> 2005,
> that
> ?
>|||Some tables are flagged as user tables even though they are shipped with the
product. The backup
history tables are such examples.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:uIVVeYi1GHA.4300@.TK2MSFTNGP03.phx.g
bl...
> Hi,
> Regarding your suggestion for the system table I tried the same thing a
s told by you "select *
> from msdb..backupfile"
> But in the profiler it still returns that as a User table instead of a
System table.
> Any ideas ?
> Thanks
> P
> "Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
> news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
>sql

Database Id prb !

Hi,
I was profiling the Audit Schema Object Access Event in SQL Server 2005,
and when I executed the system stored procedure "sys.xp_msver" I found that
the database id of the this procedure is shown as "32767" which doesn't
exists in the sys.databases view ? Any idea to what database it belongs to ?
Does the above belong to the "mssqlsystemresource" or "distmdl"
databases ?
I also wanted information as to how do I query the system tables, it
gives me an error saying the object doesnt exists
Experts pls help
TIA
Thanks
P[1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
any database.
- Breaking changes to database engine features in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms143179.aspx
[2] To query a system table, you will need to prefix the table with the
appropriate database that you are going to query. E.g.,
SELECT * FROM msdb..backupfile
- Querying the SQL Server System Catalog
http://msdn2.microsoft.com/en-us/library/ms189082.aspx
- Querying the SQL Server System Catalog FAQ
http://msdn2.microsoft.com/en-us/library/ms345522.aspx
--
Martin Poon
Microsoft MVP - SQL Server
----
- SQL Server 2005 books online
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
- SQL Server 2000 books online
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
=================================================="Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server
2005,
> and when I executed the system stored procedure "sys.xp_msver" I found
that
> the database id of the this procedure is shown as "32767" which doesn't
> exists in the sys.databases view ? Any idea to what database it belongs to
?
> Does the above belong to the "mssqlsystemresource" or "distmdl"
> databases ?
> I also wanted information as to how do I query the system tables, it
> gives me an error saying the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Yes, sys.xp_msver lives in the system resource database. You can see this by connecting through the
dedicated administrator connection (DAC), looking at the source code from the view sys.databases and
then do a select directly against the physical system table that the view sys.databases uses.
I don't know what you want to achieve in the end, but best bet is probably to disregard objects that
lives in the database with id 32767.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server 2005, and when I executed
> the system stored procedure "sys.xp_msver" I found that the database id of the this procedure is
> shown as "32767" which doesn't exists in the sys.databases view ? Any idea to what database it
> belongs to ?
> Does the above belong to the "mssqlsystemresource" or "distmdl" databases ?
> I also wanted information as to how do I query the system tables, it gives me an error saying
> the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Hi,
Thnx for your information, but I have a question, this must be a naive
for you.
But if I query from a database "db" context shouldnt the profiler show
that the database name is "db" and database id is the id the database "db" ?
Same thing for the tables also ?
The sys.all_objects lists that these are system tables and system stored
procedures but then why it is not showing that it comes from this database
in the profiler?
I want to say to the profiler that I want to monitor all the "Audit
Schema Object Access Event" in this particular database
But then since these SP's and tables have the database id different then
what is configured it is ignored even when these SP's are fired in the
database context of what is configured
Any suggestions ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with the
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> ==================================================> "Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
>> Hi,
>> I was profiling the Audit Schema Object Access Event in SQL Server
> 2005,
>> and when I executed the system stored procedure "sys.xp_msver" I found
> that
>> the database id of the this procedure is shown as "32767" which doesn't
>> exists in the sys.databases view ? Any idea to what database it belongs
>> to
> ?
>> Does the above belong to the "mssqlsystemresource" or "distmdl"
>> databases ?
>> I also wanted information as to how do I query the system tables, it
>> gives me an error saying the object doesnt exists
>> Experts pls help
>> TIA
>> Thanks
>> P
>>
>|||Hi,
Regarding your suggestion for the system table I tried the same thing as
told by you "select * from msdb..backupfile"
But in the profiler it still returns that as a User table instead of a
System table.
Any ideas ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with the
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> ==================================================> "Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
>> Hi,
>> I was profiling the Audit Schema Object Access Event in SQL Server
> 2005,
>> and when I executed the system stored procedure "sys.xp_msver" I found
> that
>> the database id of the this procedure is shown as "32767" which doesn't
>> exists in the sys.databases view ? Any idea to what database it belongs
>> to
> ?
>> Does the above belong to the "mssqlsystemresource" or "distmdl"
>> databases ?
>> I also wanted information as to how do I query the system tables, it
>> gives me an error saying the object doesnt exists
>> Experts pls help
>> TIA
>> Thanks
>> P
>>
>|||Some tables are flagged as user tables even though they are shipped with the product. The backup
history tables are such examples.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:uIVVeYi1GHA.4300@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Regarding your suggestion for the system table I tried the same thing as told by you "select *
> from msdb..backupfile"
> But in the profiler it still returns that as a User table instead of a System table.
> Any ideas ?
> Thanks
> P
> "Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
> news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
>> any database.
>> - Breaking changes to database engine features in SQL Server 2005
>> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
>> [2] To query a system table, you will need to prefix the table with the
>> appropriate database that you are going to query. E.g.,
>> SELECT * FROM msdb..backupfile
>> - Querying the SQL Server System Catalog
>> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
>> - Querying the SQL Server System Catalog FAQ
>> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
>> --
>> Martin Poon
>> Microsoft MVP - SQL Server
>> ----
>> - SQL Server 2005 books online
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> - SQL Server 2000 books online
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>> ==================================================>> "Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
>> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
>> Hi,
>> I was profiling the Audit Schema Object Access Event in SQL Server
>> 2005,
>> and when I executed the system stored procedure "sys.xp_msver" I found
>> that
>> the database id of the this procedure is shown as "32767" which doesn't
>> exists in the sys.databases view ? Any idea to what database it belongs to
>> ?
>> Does the above belong to the "mssqlsystemresource" or "distmdl"
>> databases ?
>> I also wanted information as to how do I query the system tables, it
>> gives me an error saying the object doesnt exists
>> Experts pls help
>> TIA
>> Thanks
>> P
>>
>>
>

Database hung after failure of large update query

I tried to run a large update query last night that did not complete.
But now I cannot access that database. In fact, when I click on Databases
in Enterprise Manager, that hangs and shows an hourglass.
I suspect that it failed due to a disc space. When I look at the process
status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working'
on that database.
I don't really need to complete the update (partial complete would be fine).
I just need to free up the database so I can work on the database.
Any thoughts?
- Jesse
Jesse A wrote:
> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on
> Databases in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the
> process status, I see a SPID that has a wait type of PAGEIOLatch_EX
> that is 'working' on that database.
> I don't really need to complete the update (partial complete would be
> fine). I just need to free up the database so I can work on the
> database.
> Any thoughts?
There is no such thing as a partial update unless you were using batches
to perform the updates in the first place. What likely happened is that
you ran out of space and SQL Server may be in the process of rolling
back the transaction (which can take just as long as the original update
took).
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||If u dont want any transaction for that particular SPID then u will kill the
particular process by KILL SPID
hope this will help
Herbert
"Jesse A" wrote:

> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on Databases
> in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the process
> status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working'
> on that database.
> I don't really need to complete the update (partial complete would be fine).
> I just need to free up the database so I can work on the database.
> Any thoughts?
> --
> - Jesse

Database hung after failure of large update query

I tried to run a large update query last night that did not complete.
But now I cannot access that database. In fact, when I click on Databases
in Enterprise Manager, that hangs and shows an hourglass.
I suspect that it failed due to a disc space. When I look at the process
status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working'
on that database.
I don't really need to complete the update (partial complete would be fine).
I just need to free up the database so I can work on the database.
Any thoughts?
--
- JesseJesse A wrote:
> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on
> Databases in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the
> process status, I see a SPID that has a wait type of PAGEIOLatch_EX
> that is 'working' on that database.
> I don't really need to complete the update (partial complete would be
> fine). I just need to free up the database so I can work on the
> database.
> Any thoughts?
There is no such thing as a partial update unless you were using batches
to perform the updates in the first place. What likely happened is that
you ran out of space and SQL Server may be in the process of rolling
back the transaction (which can take just as long as the original update
took).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If u dont want any transaction for that particular SPID then u will kill the
particular process by KILL SPID
hope this will help
--
Herbert
"Jesse A" wrote:
> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on Databases
> in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the process
> status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working'
> on that database.
> I don't really need to complete the update (partial complete would be fine).
> I just need to free up the database so I can work on the database.
> Any thoughts?
> --
> - Jesse

Database hung after failure of large update query

I tried to run a large update query last night that did not complete.
But now I cannot access that database. In fact, when I click on Databases
in Enterprise Manager, that hangs and shows an hourglass.
I suspect that it failed due to a disc space. When I look at the process
status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working
'
on that database.
I don't really need to complete the update (partial complete would be fine).
I just need to free up the database so I can work on the database.
Any thoughts?
--
- JesseJesse A wrote:
> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on
> Databases in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the
> process status, I see a SPID that has a wait type of PAGEIOLatch_EX
> that is 'working' on that database.
> I don't really need to complete the update (partial complete would be
> fine). I just need to free up the database so I can work on the
> database.
> Any thoughts?
There is no such thing as a partial update unless you were using batches
to perform the updates in the first place. What likely happened is that
you ran out of space and SQL Server may be in the process of rolling
back the transaction (which can take just as long as the original update
took).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If u dont want any transaction for that particular SPID then u will kill the
particular process by KILL SPID
hope this will help
Herbert
"Jesse A" wrote:

> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on Database
s
> in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the process
> status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'worki
ng'
> on that database.
> I don't really need to complete the update (partial complete would be fine
).
> I just need to free up the database so I can work on the database.
> Any thoughts?
> --
> - Jessesql

Database Help

I have am MSSQL Server.
I have a Database that has a table in it that I have been trying to access
with 3rd Party software. Well do to the way that the DB is setup I cannot
get to the data in the table how I want. so my thoughts were to have only
the parts I want go into another (local) MSSQL Database. Can I do this? If
so how do I set it up? I have the new Database created but I don't know how
to do a live copy of the data from the existing database.
Any help you could provide would really be appreciated.> I cannot get to the data in the table how I want.
Can you be more specific? What does the data look like, and how do you want
it to look?|||Sure
The data I am Looking to access is in a table called dbo.reading
The data looks like this
Device ID Sequence# Time
reg_seq tier data_desc sensor data_register
time_stamp status
052461368 0 4/14/2008 7:50:40 PM 0 0 3 NULL 2 NULL
8
052461368 0 4/14/2008 7:50:40 PM 1 0 1000001 036497.900543861091 NULL
8
052461368 0 4/14/2008 7:50:40 PM 2 0 4000001 128.900000430643559 NULL
8
052461368 0 4/14/2008 7:50:40 PM 3 0 6000001 1 614.4500091560185NULL8052461368 0 4/14/2008 7:50:40 PM 4 0 2000001 1
585.54998779296875 NULL 8
Each Device ID is a meter. However there are 4 sets of data (reg_seq)
associated with each reading per meter and I want to get the data that
corresponds with reg_seq being 1 for each data point
I created a new table called dbo.kw in the same global database
The problem I am having is I am not sure how to link the reading table to
the kW table to get what I want or set anything up so that I can do a live
output of dbo.reading.
Does this help?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:8535B201-2D67-4EF1-B81F-A7833CD03E1E@.microsoft.com...
>> I cannot get to the data in the table how I want.
> Can you be more specific? What does the data look like, and how do you
> want it to look?|||> Does this help?
Not really. Instead of:
> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
> associated with each reading per meter and I want to get the data that
> corresponds with reg_seq being 1 for each data point
Can you SHOW what you want the actual output to look like, based on the
sample rows you provided?|||Sorry about that when I pasted it all the tables lined up.
What I am looking to do is take the table dbo.reading and
export/link/synch/output the table dbo.kW
In dbo.reading I have several columns of information what I want is
Device_ID
Time
data_register (where data_desc. = 1)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:CA7EDCBA-FC7D-422F-9A29-8013CBDECF4D@.microsoft.com...
>> Does this help?
> Not really. Instead of:
>> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
>> associated with each reading per meter and I want to get the data that
>> corresponds with reg_seq being 1 for each data point
> Can you SHOW what you want the actual output to look like, based on the
> sample rows you provided?|||Anyone help?
"Rodney James" <rmjames007@.carolina.rr.com> wrote in message
news:emI0viXpIHA.4620@.TK2MSFTNGP06.phx.gbl...
> Sorry about that when I pasted it all the tables lined up.
> What I am looking to do is take the table dbo.reading and
> export/link/synch/output the table dbo.kW
> In dbo.reading I have several columns of information what I want is
> Device_ID
> Time
> data_register (where data_desc. = 1)
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:CA7EDCBA-FC7D-422F-9A29-8013CBDECF4D@.microsoft.com...
>> Does this help?
>> Not really. Instead of:
>> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
>> associated with each reading per meter and I want to get the data that
>> corresponds with reg_seq being 1 for each data point
>> Can you SHOW what you want the actual output to look like, based on the
>> sample rows you provided?
>|||I was trying to help. I asked for specific information and got more vague
information. <shrug>
"Rodney James" <rmjames007@.carolina.rr.com> wrote in message
news:elMy0fhpIHA.1164@.TK2MSFTNGP04.phx.gbl...
> Anyone help?
> "Rodney James" <rmjames007@.carolina.rr.com> wrote in message
> news:emI0viXpIHA.4620@.TK2MSFTNGP06.phx.gbl...
>> Sorry about that when I pasted it all the tables lined up.
>> What I am looking to do is take the table dbo.reading and
>> export/link/synch/output the table dbo.kW
>> In dbo.reading I have several columns of information what I want is
>> Device_ID
>> Time
>> data_register (where data_desc. = 1)
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:CA7EDCBA-FC7D-422F-9A29-8013CBDECF4D@.microsoft.com...
>> Does this help?
>> Not really. Instead of:
>> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
>> associated with each reading per meter and I want to get the data that
>> corresponds with reg_seq being 1 for each data point
>> Can you SHOW what you want the actual output to look like, based on the
>> sample rows you provided?|||I apologize Aaon. Let me phrase it this way. What specific information are
you asking for? I am sorry for not being clearer.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#IBvj6ipIHA.2256@.TK2MSFTNGP05.phx.gbl...
> I was trying to help. I asked for specific information and got more vague
> information. <shrug>
>
>
> "Rodney James" <rmjames007@.carolina.rr.com> wrote in message
> news:elMy0fhpIHA.1164@.TK2MSFTNGP04.phx.gbl...
>> Anyone help?
>> "Rodney James" <rmjames007@.carolina.rr.com> wrote in message
>> news:emI0viXpIHA.4620@.TK2MSFTNGP06.phx.gbl...
>> Sorry about that when I pasted it all the tables lined up.
>> What I am looking to do is take the table dbo.reading and
>> export/link/synch/output the table dbo.kW
>> In dbo.reading I have several columns of information what I want is
>> Device_ID
>> Time
>> data_register (where data_desc. = 1)
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:CA7EDCBA-FC7D-422F-9A29-8013CBDECF4D@.microsoft.com...
>> Does this help?
>> Not really. Instead of:
>> Each Device ID is a meter. However there are 4 sets of data (reg_seq)
>> associated with each reading per meter and I want to get the data that
>> corresponds with reg_seq being 1 for each data point
>> Can you SHOW what you want the actual output to look like, based on the
>> sample rows you provided?
>

Database field length problem

Hi everyone
I had an access database running as the source for a website but it
has become too large to run correctly so it has been ported to MS-SQL
the problem is that 4 of the fields were Memo fields in access and as
such are 5000+ characters long each this overflows the allowed size on
the SQL server (8192)

Is there a way round without splitting those 4 fields into seperate
tales?? as this would cause a truly major re-write of the website

Thanks for any help
Further details available if requiredIf you use VarChar can you not set the max field length to 8000 characters?

I can't believe SQL has such limits, surely there's a way to automatically
use two rows for one record, or does this require additional programming in
ASP?

Cheers, Ash

"Peter" <peter@.iib.ws> wrote in message
news:81307dbc.0406250528.ae1d3d@.posting.google.com ...
> Hi everyone
> I had an access database running as the source for a website but it
> has become too large to run correctly so it has been ported to MS-SQL
> the problem is that 4 of the fields were Memo fields in access and as
> such are 5000+ characters long each this overflows the allowed size on
> the SQL server (8192)
> Is there a way round without splitting those 4 fields into seperate
> tales?? as this would cause a truly major re-write of the website
> Thanks for any help
> Further details available if required|||Hi there
When i try to import a flat text file where i have used the transform
tool to delare field sizes of 6000 for the four fields i get the
following error if they are varchar

cannot create a row of size 8366 which is greater than the allowable
maximum of 8060

If i map them as ntext i dont get an error but the data is truncated

Any ideas
Peter

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Peter Winning (peter@.iib.ws) writes:
> When i try to import a flat text file where i have used the transform
> tool to delare field sizes of 6000 for the four fields i get the
> following error if they are varchar
> cannot create a row of size 8366 which is greater than the allowable
> maximum of 8060
> If i map them as ntext i dont get an error but the data is truncated

ntext is probably the way to go, since that is the only way to have
more than 8060 bytes of data on one row.

Why your ntext data is truncated I don't know, but then again I don't
know how import the data. A CREATE TABLE definition and a sample data
file could help. (You would have to pack the data file into a zip
file, since it surely would be wrecked by news transport, if you
posted it as text.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi
I am using the enterprise manager to upload the database and so far all
attempts no matter how the fields are transformed are still failing
I have tried mapping the fields as vchar, nvchar and ntext with the same
results in every case ie failure to complete or data truncation this has
also been attempted on the server itself
The upsizing wizard in access also failed to do the job

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Peter Winning (peter@.iib.ws) writes:
> I am using the enterprise manager to upload the database and so far all
> attempts no matter how the fields are transformed are still failing
> I have tried mapping the fields as vchar, nvchar and ntext with the same
> results in every case ie failure to complete or data truncation this has
> also been attempted on the server itself
> The upsizing wizard in access also failed to do the job

I have no idea what Enterprise Manager is up to when it imports data;
I didn't even know that it had a function for it, and even less have I
used it.

My general experience of EM, though, is that it tends to occlude some
syntax in order to be helpful, when things go over its head, it leaves
you alone in the dark.

I would try to import the file with BCP, but since I don't know how your
text file looks like, I cannot suggest the exact command line. I repeat
from my previous posting:

A CREATE TABLE definition and a sample data file could help. (You would
have to pack the data file into a zip file, since it surely would be
wrecked by news transport, if you posted it as text.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you
I have to confess that i am a complete novice with sql what is "BCP" and
where would i find some help in how to use it.
The database is a flat field one with about 30 fields 4 of which are
memo fields with very large amounts of data in each

Regards
Peter

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Peter Winning (peter@.iib.ws) writes:
> I have to confess that i am a complete novice with sql what is "BCP" and
> where would i find some help in how to use it.

BCP is a command line tool that permits you to load large amount of
data from files. The files can be text files or binary. BCP is a bit
restricted in that the file has to be fairly square. That is, it not able
to sort out headers, unless you can find a square hole to put them in.

You can read more about BCP in Books Online.

Another alternative is DTS (Data Transformation Service), which is a more
versatile load tool, which I have never used my self though.

> The database is a flat field one with about 30 fields 4 of which are
> memo fields with very large amounts of data in each

Well, it is up to you. If you don't want to post a CREATE TABLE statement
for your table and a sample data file, you don't have to. But then you will
have to find out how to load your file with BCP on your own, because I
don't really feel like guessing your table and data.

If you look in the SQL Server Program group, there is "Import and
Export Data". This takes you to the DTS wizard, which may be able to
guide all the way. But as I said, I have not used DTS. Then again,
there are some nice people in microsoft.public.sqlserver.dts who might
be able to help you if you go that way. But they, too, might want the
table definition and sample data.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

I don't think the DTS Import wizard will truncate the data if you import it
from an Access database! It could be that you are selecting the data in
Query Analyser which has a configurable value (Tools/Options/Results/Maximum
characters per column).

If you have a table such as

CREATE TABLE MyAccessTable ( id int, Memo1 ntext, Memo2 ntext, Memo3 ntext,
Memo4 ntext )

The you can see the number of characters using:
SELECT id,
datalength(memo1)/2,datalength(memo2)/2,datalength(memo3)/2,datalength(memo4
)/2 FROM MyAccessTable

John

"Peter Winning" <peter@.iib.ws> wrote in message
news:40dc5ecd$0$16435$c397aba@.news.newsgroups.ws.. .
> Hi there
> When i try to import a flat text file where i have used the transform
> tool to delare field sizes of 6000 for the four fields i get the
> following error if they are varchar
> cannot create a row of size 8366 which is greater than the allowable
> maximum of 8060
> If i map them as ntext i dont get an error but the data is truncated
> Any ideas
> Peter
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||I've just looked at our WebMail application we're using, it uses a SQL
database to store the messages which generally are over 8000 characters - it
uses NTEXT as the datatype, with a length of '16', how on EARTH does that
relate to a VARCHAR field that has to be set to 8000??

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9514A53A31CFFYazorman@.127.0.0.1...
> Peter Winning (peter@.iib.ws) writes:
> > When i try to import a flat text file where i have used the transform
> > tool to delare field sizes of 6000 for the four fields i get the
> > following error if they are varchar
> > cannot create a row of size 8366 which is greater than the allowable
> > maximum of 8060
> > If i map them as ntext i dont get an error but the data is truncated
> ntext is probably the way to go, since that is the only way to have
> more than 8060 bytes of data on one row.
> Why your ntext data is truncated I don't know, but then again I don't
> know how import the data. A CREATE TABLE definition and a sample data
> file could help. (You would have to pack the data file into a zip
> file, since it surely would be wrecked by news transport, if you
> posted it as text.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||J. Hall (remove_this_ash@.a-hall.com) writes:
> I've just looked at our WebMail application we're using, it uses a SQL
> database to store the messages which generally are over 8000 characters
> - it uses NTEXT as the datatype, with a length of '16', how on EARTH
> does that relate to a VARCHAR field that has to be set to 8000??

16 is the length of the pointer that is stored within the row. The data
itself is stored on separate pages. A varchar value on the other hand is
stored within the row, and since a row can not host more than 8060 bytes
of data, there is an upper limit.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Excellent thanks for clearing that up.

Many thanks,

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9517834BC4F9AYazorman@.127.0.0.1...
> J. Hall (remove_this_ash@.a-hall.com) writes:
> > I've just looked at our WebMail application we're using, it uses a SQL
> > database to store the messages which generally are over 8000 characters
> > - it uses NTEXT as the datatype, with a length of '16', how on EARTH
> > does that relate to a VARCHAR field that has to be set to 8000??
> 16 is the length of the pointer that is stored within the row. The data
> itself is stored on separate pages. A varchar value on the other hand is
> stored within the row, and since a row can not host more than 8060 bytes
> of data, there is an upper limit.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Database field length from dataset in vb.net

How to get the max length of numeric field in a DataSet?
I have a DataSet bound to an Access database. Is it possible to get the maximum length of numeric field of a table in the DataSet? Many fields in the database tables have maximum length values set in ...

A VB.net or ADO.Net forum would be a better start, but just walk down the object hierarchy-

myDataSet.Tables(0).Columns(0).Length

Database Export Question

Hi All,
We have a procedure here we recently moved from Access 2000 to SQL Server
2000. Unfortunately, during the migration, we missed an export that is
needed (an index file). The prior indexes were stored in a CDX file. We
have a crystal reports routine running that needs this index file for proper
linking to a legacy app.
My question is "How can I export a CDX file to duplicate the results we had
from Access 2000?"
I hope someone here might have an answer and or another solution that might
work for me!
Thanks!
TravisTravis,
SQL Server does not use separate files for indexes. If you need a
particular index on a table in sql server you can generate one using CREATE
INDEX or you can use Enterprise Manager. If crystal is looking for this
file you might try a Crystal Reports news group or the Access newsgroup.
--
Andrew J. Kelly
SQL Server MVP
"REM7600" <rem7600@.hotmail.com> wrote in message
news:OhdiZjTgDHA.616@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> We have a procedure here we recently moved from Access 2000 to SQL Server
> 2000. Unfortunately, during the migration, we missed an export that is
> needed (an index file). The prior indexes were stored in a CDX file. We
> have a crystal reports routine running that needs this index file for
proper
> linking to a legacy app.
> My question is "How can I export a CDX file to duplicate the results we
had
> from Access 2000?"
> I hope someone here might have an answer and or another solution that
might
> work for me!
> Thanks!
> Travis
>|||A CDX file is not a native Access 2000 file (unless it's simply a MDB file
that has been renamed).
Possibly you have a FoxPro database (DBF) linked into Access? FoxPro uses
the CDX file extension for its indexes.
> have a crystal reports routine running that needs this index file for
proper
> linking to a legacy app.
Presumably this routine is looking for a unqiue key which you can easily
create yourself either as a PRIMARY KEY or UNIQUE constraint or using CREATE
INDEX:
CREATE UNIQUE [NON]CLUSTERED INDEX...
See Books Online for details.
There should be no need to import the index itself because an index doesn't
contain any additional data, but if it is a FoxPro index then the Visual
FoxPro upsizing wizard would be an alternative method of recreating it in
SQL.
--
David Portas
--
Please reply only to the newsgroup
--|||Hey David,
I already have created the indexes in SQL Server but the Crystal app is not
recognizing these as being unique.
Thanks for the help. I'm gonna have to find another way areound this one...
Travis
> A CDX file is not a native Access 2000 file (unless it's simply a MDB file
> that has been renamed).
> Possibly you have a FoxPro database (DBF) linked into Access? FoxPro uses
> the CDX file extension for its indexes.
>
> > have a crystal reports routine running that needs this index file for
> proper
> > linking to a legacy app.
> Presumably this routine is looking for a unqiue key which you can easily
> create yourself either as a PRIMARY KEY or UNIQUE constraint or using
CREATE
> INDEX:
> CREATE UNIQUE [NON]CLUSTERED INDEX...
> See Books Online for details.
> There should be no need to import the index itself because an index
doesn't
> contain any additional data, but if it is a FoxPro index then the Visual
> FoxPro upsizing wizard would be an alternative method of recreating it in
> SQL.|||Hey Andrew,
I was afraid that was going to be the answer! OUCH! :-)
I already have the indexes created on the table so I guess I'll need to look
at doing it differently in Crystal. Oh, how these little projects grow...
:-) Thanks for the reply.
Travis
> SQL Server does not use separate files for indexes. If you need a
> particular index on a table in sql server you can generate one using
CREATE
> INDEX or you can use Enterprise Manager. If crystal is looking for this
> file you might try a Crystal Reports news group or the Access newsgroup.sql

Database Encryption - Employee performance review

Hi,
I am writing an .NET 2.0 app that has different users logging in who
have different access levels in the app. One of the user roles is "HR".
When the user logs in with these credentials, they have a whole heap of
buttons/links/pages related to HR stuff that normal team members don't.
One of the sensitive information that needs to be displayed and more
importantly, stored in the database is the employee performance reviews
(HR access only).
I am wondering how I would go about storing/maintaining this
information as even the DBA's are not supposed to have access to this
information, but should still be able to administer it and/or retrieve
the encryption keys if the HR personnel forget their passwords etc.
The simplest way I can think of is to issue all the HR people with one
password, encrypt the data in the DB layer (in code) and then store it
in a table in the database. This way, anyone with access to the
Database won't necessarily have access to the data. The problem with
this one is that all the HR personnel share one password, which doesn't
seem like a nice (secure) solution.
The other option was to store the encryption key in the database and
encrypt the key itself with the password provided by the individual
user (HR person), which will be stored against that user's record. This
way, whenever a data request is made, the database sends the encrypted
performance review data and the password encrypted key to the user. The
user will then decrypt the key using their password, and then decrypt
the data using the key. This way, if the user ever forgets their
password, all the DBA (and/or App with owner acess) has to do is
reencrypt the key with a new user password and store it against the
user's record. The problem with this is storing the key in the database
and yet restricting access to the DBA. They should not have raw access
to the key (which essentially means they can see the data in the
database), but still should have enough access that if a user forgets
their password, the DBA can reset the password without losing all the
existing data.
I was thinking of storing the key on a key server somewhere with
different access rights etc. but that means the system starts to get
complex.
How would you guys go about building such a system? Does MS Sql Server
2005 provide any mechanisms for such functionality? Plus, as a design
issue, is it better to let the database handle the encryption, key
management, roles/privileges or is it better to do it in the
application itself? One of the advantages I can think of for doing it
in the application is that it gives a bit of database independence, i
can change the underlying database easily without having to rewrite a
whole heap of functionality. The other reason is that this way, i won't
have unencrypted data or keys on wire.
Looking forward to some expert opinions and discussions regarding this
problem.
Thanks in advance.Hello? Anyone? Surely this is not such a hard problem, or particularly
unusual. I would guess there are many times when you would want to
encrypt the data in a database, so as to make it inaccessible to the
DBA's, but still give them some administrative privileges. One of the
constraints is that the user with access to the data doesn't
necessarily "own" the data, so that if they forget the password (i.e
lose the encryption key), all the data is not lost.
On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
> Hi,
> I am writing an .NET 2.0 app that has different users logging in who
> have different access levels in the app. One of the user roles is "HR".
> When the user logs in with these credentials, they have a whole heap of
> buttons/links/pages related to HR stuff that normal team members don't.
> One of the sensitive information that needs to be displayed and more
> importantly, stored in the database is the employee performance reviews
> (HR access only).
> I am wondering how I would go about storing/maintaining this
> information as even the DBA's are not supposed to have access to this
> information, but should still be able to administer it and/or retrieve
> the encryption keys if the HR personnel forget their passwords etc.
> The simplest way I can think of is to issue all the HR people with one
> password, encrypt the data in the DB layer (in code) and then store it
> in a table in the database. This way, anyone with access to the
> Database won't necessarily have access to the data. The problem with
> this one is that all the HR personnel share one password, which doesn't
> seem like a nice (secure) solution.
> The other option was to store the encryption key in the database and
> encrypt the key itself with the password provided by the individual
> user (HR person), which will be stored against that user's record. This
> way, whenever a data request is made, the database sends the encrypted
> performance review data and the password encrypted key to the user. The
> user will then decrypt the key using their password, and then decrypt
> the data using the key. This way, if the user ever forgets their
> password, all the DBA (and/or App with owner acess) has to do is
> reencrypt the key with a new user password and store it against the
> user's record. The problem with this is storing the key in the database
> and yet restricting access to the DBA. They should not have raw access
> to the key (which essentially means they can see the data in the
> database), but still should have enough access that if a user forgets
> their password, the DBA can reset the password without losing all the
> existing data.
> I was thinking of storing the key on a key server somewhere with
> different access rights etc. but that means the system starts to get
> complex.
> How would you guys go about building such a system? Does MS Sql Server
> 2005 provide any mechanisms for such functionality? Plus, as a design
> issue, is it better to let the database handle the encryption, key
> management, roles/privileges or is it better to do it in the
> application itself? One of the advantages I can think of for doing it
> in the application is that it gives a bit of database independence, i
> can change the underlying database easily without having to rewrite a
> whole heap of functionality. The other reason is that this way, i won't
> have unencrypted data or keys on wire.
> Looking forward to some expert opinions and discussions regarding this
> problem.
> Thanks in advance.|||The key (ahem) here is this:
>> Whoever has access to the decryption key has access to the data. <<
Plain and simple. If the DBAs can read the decryption key, they can
read the data. So the statement "but should still be able to administer
it and/or retrieve the encryption keys if the HR personnel forget their
passwords etc." is what I call a "non-starter". By definition, the DBAs
have access to the data.
You're right on one point though. If all people share access to the
same password or decryption key, you have an insecure solution. Think
of it this way -- the bigger the "secret" is, and the more people who
have access to that secret, the less secure the system.
Encrypting the key doesn't work either because you have to encrypt the
key with another key. Where are you going to put that key? Are the
DBAs going to have access to it too?
Here's my take on it:
Carefully analyze what is and what is not a secret in the system. Only
encrypt the real secrets. Limit the access to the database to the
smallest set of people possible. If you don't want the DBAs to have
access to the encryption key, that by definition means you have to store
the key outside the database. I recommend that you use .NET's
encryption routines to encrypt your secrets *outside* of the database,
and simply store the data in the system once it's encrypted.
So it boils down to this. SOMEBODY needs access to the keys. They need
to exist somewhere (even if it's not on your SQL Server). The
administrator of that machine will probably be able to read the key.
Deny that person read access to the database. That person needs to be
trustworthy, too. If he/she is not, they can share the key with an
untrustworthy DBA and get your data by writing a small program.
HTH
-Dave
nyathancha@.hotmail.com wrote:
> Hello? Anyone? Surely this is not such a hard problem, or particularly
> unusual. I would guess there are many times when you would want to
> encrypt the data in a database, so as to make it inaccessible to the
> DBA's, but still give them some administrative privileges. One of the
> constraints is that the user with access to the data doesn't
> necessarily "own" the data, so that if they forget the password (i.e
> lose the encryption key), all the data is not lost.
>
>
>
> On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
>> Hi,
>> I am writing an .NET 2.0 app that has different users logging in who
>> have different access levels in the app. One of the user roles is "HR".
>> When the user logs in with these credentials, they have a whole heap of
>> buttons/links/pages related to HR stuff that normal team members don't.
>> One of the sensitive information that needs to be displayed and more
>> importantly, stored in the database is the employee performance reviews
>> (HR access only).
>> I am wondering how I would go about storing/maintaining this
>> information as even the DBA's are not supposed to have access to this
>> information, but should still be able to administer it and/or retrieve
>> the encryption keys if the HR personnel forget their passwords etc.
>> The simplest way I can think of is to issue all the HR people with one
>> password, encrypt the data in the DB layer (in code) and then store it
>> in a table in the database. This way, anyone with access to the
>> Database won't necessarily have access to the data. The problem with
>> this one is that all the HR personnel share one password, which doesn't
>> seem like a nice (secure) solution.
>> The other option was to store the encryption key in the database and
>> encrypt the key itself with the password provided by the individual
>> user (HR person), which will be stored against that user's record. This
>> way, whenever a data request is made, the database sends the encrypted
>> performance review data and the password encrypted key to the user. The
>> user will then decrypt the key using their password, and then decrypt
>> the data using the key. This way, if the user ever forgets their
>> password, all the DBA (and/or App with owner acess) has to do is
>> reencrypt the key with a new user password and store it against the
>> user's record. The problem with this is storing the key in the database
>> and yet restricting access to the DBA. They should not have raw access
>> to the key (which essentially means they can see the data in the
>> database), but still should have enough access that if a user forgets
>> their password, the DBA can reset the password without losing all the
>> existing data.
>> I was thinking of storing the key on a key server somewhere with
>> different access rights etc. but that means the system starts to get
>> complex.
>> How would you guys go about building such a system? Does MS Sql Server
>> 2005 provide any mechanisms for such functionality? Plus, as a design
>> issue, is it better to let the database handle the encryption, key
>> management, roles/privileges or is it better to do it in the
>> application itself? One of the advantages I can think of for doing it
>> in the application is that it gives a bit of database independence, i
>> can change the underlying database easily without having to rewrite a
>> whole heap of functionality. The other reason is that this way, i won't
>> have unencrypted data or keys on wire.
>> Looking forward to some expert opinions and discussions regarding this
>> problem.
>> Thanks in advance.
>
-Dave Markle
http://www.markleconsulting.com/blog|||Hi Dave, Thanks for the reply.
I was thinking along the same lines as well. I couldn't think of anyway
of storing the key in the database and still restricting access to the
DBA. Which is why I mentioned in my original post the "key server
somewhere with
different access rights" . Unless sql server has some magic
features/functionality that I don't know about.
So how hard would it be to build/deploy a key server? Maybe this is
more of an ecryption/security question rather than a database question.
My idea of encrypting the key with another key was that the second
"key" would be the user's password. This way the DBA doesn't have
access to the original data encryption key even if its stored in the
database (because it can only be decrypted using the user's password),
but if the user does forget their password, the data is not lost
because it wasn't encrypted with their password(/key). The DBA can
"reset" their password by reencrypting the orginal data key with a new
user password key and storing it against the column. Which is where I
came to the key server. The fact that the DBA should be able to get the
data key encrypted with a user password, but not the raw data key
itself. I can't think of anyway of doing this without an independent
application.
Does anyone know any commercial encryption key storage and distribution
solutions/products?
I am sure at some stage the data encryption key will itself have to be
encrypted before being put on the wire. Other wise you would have the
encrypted data coming from the database, the plain encryption key
coming from the key server. Doesn't seem particularly secure.
What is the general industry solution to a situation like this, where
you want to store sensitive data in a database, but the data is
accessible (i.e belongs to) to more than one person (so that if that
one person forgets their password/key, the data itself is not lost)
On Jan 25, 1:28 pm, Dave Markle <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com>
wrote:
> The key (ahem) here is this:
> >> Whoever has access to the decryption key has access to the data. <<
> Plain and simple. If the DBAs can read the decryption key, they can
> read the data. So the statement "but should still be able to administer
> it and/or retrieve the encryption keys if the HR personnel forget their
> passwords etc." is what I call a "non-starter". By definition, the DBAs
> have access to the data.
> You're right on one point though. If all people share access to the
> same password or decryption key, you have an insecure solution. Think
> of it this way -- the bigger the "secret" is, and the more people who
> have access to that secret, the less secure the system.
> Encrypting the key doesn't work either because you have to encrypt the
> key with another key. Where are you going to put that key? Are the
> DBAs going to have access to it too?
> Here's my take on it:
> Carefully analyze what is and what is not a secret in the system. Only
> encrypt the real secrets. Limit the access to the database to the
> smallest set of people possible. If you don't want the DBAs to have
> access to the encryption key, that by definition means you have to store
> the key outside the database. I recommend that you use .NET's
> encryption routines to encrypt your secrets *outside* of the database,
> and simply store the data in the system once it's encrypted.
> So it boils down to this. SOMEBODY needs access to the keys. They need
> to exist somewhere (even if it's not on your SQL Server). The
> administrator of that machine will probably be able to read the key.
> Deny that person read access to the database. That person needs to be
> trustworthy, too. If he/she is not, they can share the key with an
> untrustworthy DBA and get your data by writing a small program.
> HTH
> -Dave
>
> nyathan...@.hotmail.com wrote:
> > Hello? Anyone? Surely this is not such a hard problem, or particularly
> > unusual. I would guess there are many times when you would want to
> > encrypt the data in a database, so as to make it inaccessible to the
> > DBA's, but still give them some administrative privileges. One of the
> > constraints is that the user with access to the data doesn't
> > necessarily "own" the data, so that if they forget the password (i.e
> > lose the encryption key), all the data is not lost.
> > On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
> >> Hi,
> >> I am writing an .NET 2.0 app that has different users logging in who
> >> have different access levels in the app. One of the user roles is "HR".
> >> When the user logs in with these credentials, they have a whole heap of
> >> buttons/links/pages related to HR stuff that normal team members don't.
> >> One of the sensitive information that needs to be displayed and more
> >> importantly, stored in the database is the employee performance reviews
> >> (HR access only).
> >> I am wondering how I would go about storing/maintaining this
> >> information as even the DBA's are not supposed to have access to this
> >> information, but should still be able to administer it and/or retrieve
> >> the encryption keys if the HR personnel forget their passwords etc.
> >> The simplest way I can think of is to issue all the HR people with one
> >> password, encrypt the data in the DB layer (in code) and then store it
> >> in a table in the database. This way, anyone with access to the
> >> Database won't necessarily have access to the data. The problem with
> >> this one is that all the HR personnel share one password, which doesn't
> >> seem like a nice (secure) solution.
> >> The other option was to store the encryption key in the database and
> >> encrypt the key itself with the password provided by the individual
> >> user (HR person), which will be stored against that user's record. This
> >> way, whenever a data request is made, the database sends the encrypted
> >> performance review data and the password encrypted key to the user. The
> >> user will then decrypt the key using their password, and then decrypt
> >> the data using the key. This way, if the user ever forgets their
> >> password, all the DBA (and/or App with owner acess) has to do is
> >> reencrypt the key with a new user password and store it against the
> >> user's record. The problem with this is storing the key in the database
> >> and yet restricting access to the DBA. They should not have raw access
> >> to the key (which essentially means they can see the data in the
> >> database), but still should have enough access that if a user forgets
> >> their password, the DBA can reset the password without losing all the
> >> existing data.
> >> I was thinking of storing the key on a key server somewhere with
> >> different access rights etc. but that means the system starts to get
> >> complex.
> >> How would you guys go about building such a system? Does MS Sql Server
> >> 2005 provide any mechanisms for such functionality? Plus, as a design
> >> issue, is it better to let the database handle the encryption, key
> >> management, roles/privileges or is it better to do it in the
> >> application itself? One of the advantages I can think of for doing it
> >> in the application is that it gives a bit of database independence, i
> >> can change the underlying database easily without having to rewrite a
> >> whole heap of functionality. The other reason is that this way, i won't
> >> have unencrypted data or keys on wire.
> >> Looking forward to some expert opinions and discussions regarding this
> >> problem.
> >> Thanks in advance.--
> -Dave Markle
> http://www.markleconsulting.com/blog

Database Encryption - Employee performance review

Hi,
I am writing an .NET 2.0 app that has different users logging in who
have different access levels in the app. One of the user roles is "HR".
When the user logs in with these credentials, they have a whole heap of
buttons/links/pages related to HR stuff that normal team members don't.
One of the sensitive information that needs to be displayed and more
importantly, stored in the database is the employee performance reviews
(HR access only).
I am wondering how I would go about storing/maintaining this
information as even the DBA's are not supposed to have access to this
information, but should still be able to administer it and/or retrieve
the encryption keys if the HR personnel forget their passwords etc.
The simplest way I can think of is to issue all the HR people with one
password, encrypt the data in the DB layer (in code) and then store it
in a table in the database. This way, anyone with access to the
Database won't necessarily have access to the data. The problem with
this one is that all the HR personnel share one password, which doesn't
seem like a nice (secure) solution.
The other option was to store the encryption key in the database and
encrypt the key itself with the password provided by the individual
user (HR person), which will be stored against that user's record. This
way, whenever a data request is made, the database sends the encrypted
performance review data and the password encrypted key to the user. The
user will then decrypt the key using their password, and then decrypt
the data using the key. This way, if the user ever forgets their
password, all the DBA (and/or App with owner acess) has to do is
reencrypt the key with a new user password and store it against the
user's record. The problem with this is storing the key in the database
and yet restricting access to the DBA. They should not have raw access
to the key (which essentially means they can see the data in the
database), but still should have enough access that if a user forgets
their password, the DBA can reset the password without losing all the
existing data.
I was thinking of storing the key on a key server somewhere with
different access rights etc. but that means the system starts to get
complex.
How would you guys go about building such a system? Does MS Sql Server
2005 provide any mechanisms for such functionality? Plus, as a design
issue, is it better to let the database handle the encryption, key
management, roles/privileges or is it better to do it in the
application itself? One of the advantages I can think of for doing it
in the application is that it gives a bit of database independence, i
can change the underlying database easily without having to rewrite a
whole heap of functionality. The other reason is that this way, i won't
have unencrypted data or keys on wire.
Looking forward to some expert opinions and discussions regarding this
problem.
Thanks in advance.
Hello? Anyone? Surely this is not such a hard problem, or particularly
unusual. I would guess there are many times when you would want to
encrypt the data in a database, so as to make it inaccessible to the
DBA's, but still give them some administrative privileges. One of the
constraints is that the user with access to the data doesn't
necessarily "own" the data, so that if they forget the password (i.e
lose the encryption key), all the data is not lost.
On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
> Hi,
> I am writing an .NET 2.0 app that has different users logging in who
> have different access levels in the app. One of the user roles is "HR".
> When the user logs in with these credentials, they have a whole heap of
> buttons/links/pages related to HR stuff that normal team members don't.
> One of the sensitive information that needs to be displayed and more
> importantly, stored in the database is the employee performance reviews
> (HR access only).
> I am wondering how I would go about storing/maintaining this
> information as even the DBA's are not supposed to have access to this
> information, but should still be able to administer it and/or retrieve
> the encryption keys if the HR personnel forget their passwords etc.
> The simplest way I can think of is to issue all the HR people with one
> password, encrypt the data in the DB layer (in code) and then store it
> in a table in the database. This way, anyone with access to the
> Database won't necessarily have access to the data. The problem with
> this one is that all the HR personnel share one password, which doesn't
> seem like a nice (secure) solution.
> The other option was to store the encryption key in the database and
> encrypt the key itself with the password provided by the individual
> user (HR person), which will be stored against that user's record. This
> way, whenever a data request is made, the database sends the encrypted
> performance review data and the password encrypted key to the user. The
> user will then decrypt the key using their password, and then decrypt
> the data using the key. This way, if the user ever forgets their
> password, all the DBA (and/or App with owner acess) has to do is
> reencrypt the key with a new user password and store it against the
> user's record. The problem with this is storing the key in the database
> and yet restricting access to the DBA. They should not have raw access
> to the key (which essentially means they can see the data in the
> database), but still should have enough access that if a user forgets
> their password, the DBA can reset the password without losing all the
> existing data.
> I was thinking of storing the key on a key server somewhere with
> different access rights etc. but that means the system starts to get
> complex.
> How would you guys go about building such a system? Does MS Sql Server
> 2005 provide any mechanisms for such functionality? Plus, as a design
> issue, is it better to let the database handle the encryption, key
> management, roles/privileges or is it better to do it in the
> application itself? One of the advantages I can think of for doing it
> in the application is that it gives a bit of database independence, i
> can change the underlying database easily without having to rewrite a
> whole heap of functionality. The other reason is that this way, i won't
> have unencrypted data or keys on wire.
> Looking forward to some expert opinions and discussions regarding this
> problem.
> Thanks in advance.
|||The key (ahem) here is this:
[vbcol=seagreen]
Plain and simple. If the DBAs can read the decryption key, they can
read the data. So the statement "but should still be able to administer
it and/or retrieve the encryption keys if the HR personnel forget their
passwords etc." is what I call a "non-starter". By definition, the DBAs
have access to the data.
You're right on one point though. If all people share access to the
same password or decryption key, you have an insecure solution. Think
of it this way -- the bigger the "secret" is, and the more people who
have access to that secret, the less secure the system.
Encrypting the key doesn't work either because you have to encrypt the
key with another key. Where are you going to put that key? Are the
DBAs going to have access to it too?
Here's my take on it:
Carefully analyze what is and what is not a secret in the system. Only
encrypt the real secrets. Limit the access to the database to the
smallest set of people possible. If you don't want the DBAs to have
access to the encryption key, that by definition means you have to store
the key outside the database. I recommend that you use .NET's
encryption routines to encrypt your secrets *outside* of the database,
and simply store the data in the system once it's encrypted.
So it boils down to this. SOMEBODY needs access to the keys. They need
to exist somewhere (even if it's not on your SQL Server). The
administrator of that machine will probably be able to read the key.
Deny that person read access to the database. That person needs to be
trustworthy, too. If he/she is not, they can share the key with an
untrustworthy DBA and get your data by writing a small program.
HTH
-Dave
nyathancha@.hotmail.com wrote:
> Hello? Anyone? Surely this is not such a hard problem, or particularly
> unusual. I would guess there are many times when you would want to
> encrypt the data in a database, so as to make it inaccessible to the
> DBA's, but still give them some administrative privileges. One of the
> constraints is that the user with access to the data doesn't
> necessarily "own" the data, so that if they forget the password (i.e
> lose the encryption key), all the data is not lost.
>
>
>
> On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
>
-Dave Markle
http://www.markleconsulting.com/blog
|||Hi Dave, Thanks for the reply.
I was thinking along the same lines as well. I couldn't think of anyway
of storing the key in the database and still restricting access to the
DBA. Which is why I mentioned in my original post the "key server
somewhere with
different access rights" . Unless sql server has some magic
features/functionality that I don't know about.
So how hard would it be to build/deploy a key server? Maybe this is
more of an ecryption/security question rather than a database question.
My idea of encrypting the key with another key was that the second
"key" would be the user's password. This way the DBA doesn't have
access to the original data encryption key even if its stored in the
database (because it can only be decrypted using the user's password),
but if the user does forget their password, the data is not lost
because it wasn't encrypted with their password(/key). The DBA can
"reset" their password by reencrypting the orginal data key with a new
user password key and storing it against the column. Which is where I
came to the key server. The fact that the DBA should be able to get the
data key encrypted with a user password, but not the raw data key
itself. I can't think of anyway of doing this without an independent
application.
Does anyone know any commercial encryption key storage and distribution
solutions/products?
I am sure at some stage the data encryption key will itself have to be
encrypted before being put on the wire. Other wise you would have the
encrypted data coming from the database, the plain encryption key
coming from the key server. Doesn't seem particularly secure.
What is the general industry solution to a situation like this, where
you want to store sensitive data in a database, but the data is
accessible (i.e belongs to) to more than one person (so that if that
one person forgets their password/key, the data itself is not lost)
On Jan 25, 1:28 pm, Dave Markle <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com>
wrote:
> The key (ahem) here is this:
>
> Plain and simple. If the DBAs can read the decryption key, they can
> read the data. So the statement "but should still be able to administer
> it and/or retrieve the encryption keys if the HR personnel forget their
> passwords etc." is what I call a "non-starter". By definition, the DBAs
> have access to the data.
> You're right on one point though. If all people share access to the
> same password or decryption key, you have an insecure solution. Think
> of it this way -- the bigger the "secret" is, and the more people who
> have access to that secret, the less secure the system.
> Encrypting the key doesn't work either because you have to encrypt the
> key with another key. Where are you going to put that key? Are the
> DBAs going to have access to it too?
> Here's my take on it:
> Carefully analyze what is and what is not a secret in the system. Only
> encrypt the real secrets. Limit the access to the database to the
> smallest set of people possible. If you don't want the DBAs to have
> access to the encryption key, that by definition means you have to store
> the key outside the database. I recommend that you use .NET's
> encryption routines to encrypt your secrets *outside* of the database,
> and simply store the data in the system once it's encrypted.
> So it boils down to this. SOMEBODY needs access to the keys. They need
> to exist somewhere (even if it's not on your SQL Server). The
> administrator of that machine will probably be able to read the key.
> Deny that person read access to the database. That person needs to be
> trustworthy, too. If he/she is not, they can share the key with an
> untrustworthy DBA and get your data by writing a small program.
> HTH
> -Dave
>
> nyathan...@.hotmail.com wrote:
>
>
>
>
> -Dave Markle
> http://www.markleconsulting.com/blog

Database Encryption - Employee performance review

Hi,
I am writing an .NET 2.0 app that has different users logging in who
have different access levels in the app. One of the user roles is "HR".
When the user logs in with these credentials, they have a whole heap of
buttons/links/pages related to HR stuff that normal team members don't.
One of the sensitive information that needs to be displayed and more
importantly, stored in the database is the employee performance reviews
(HR access only).
I am wondering how I would go about storing/maintaining this
information as even the DBA's are not supposed to have access to this
information, but should still be able to administer it and/or retrieve
the encryption keys if the HR personnel forget their passwords etc.
The simplest way I can think of is to issue all the HR people with one
password, encrypt the data in the DB layer (in code) and then store it
in a table in the database. This way, anyone with access to the
Database won't necessarily have access to the data. The problem with
this one is that all the HR personnel share one password, which doesn't
seem like a nice (secure) solution.
The other option was to store the encryption key in the database and
encrypt the key itself with the password provided by the individual
user (HR person), which will be stored against that user's record. This
way, whenever a data request is made, the database sends the encrypted
performance review data and the password encrypted key to the user. The
user will then decrypt the key using their password, and then decrypt
the data using the key. This way, if the user ever forgets their
password, all the DBA (and/or App with owner acess) has to do is
reencrypt the key with a new user password and store it against the
user's record. The problem with this is storing the key in the database
and yet restricting access to the DBA. They should not have raw access
to the key (which essentially means they can see the data in the
database), but still should have enough access that if a user forgets
their password, the DBA can reset the password without losing all the
existing data.
I was thinking of storing the key on a key server somewhere with
different access rights etc. but that means the system starts to get
complex.
How would you guys go about building such a system? Does MS Sql Server
2005 provide any mechanisms for such functionality? Plus, as a design
issue, is it better to let the database handle the encryption, key
management, roles/privileges or is it better to do it in the
application itself? One of the advantages I can think of for doing it
in the application is that it gives a bit of database independence, i
can change the underlying database easily without having to rewrite a
whole heap of functionality. The other reason is that this way, i won't
have unencrypted data or keys on wire.
Looking forward to some expert opinions and discussions regarding this
problem.
Thanks in advance.Hello? Anyone? Surely this is not such a hard problem, or particularly
unusual. I would guess there are many times when you would want to
encrypt the data in a database, so as to make it inaccessible to the
DBA's, but still give them some administrative privileges. One of the
constraints is that the user with access to the data doesn't
necessarily "own" the data, so that if they forget the password (i.e
lose the encryption key), all the data is not lost.
On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
> Hi,
> I am writing an .NET 2.0 app that has different users logging in who
> have different access levels in the app. One of the user roles is "HR".
> When the user logs in with these credentials, they have a whole heap of
> buttons/links/pages related to HR stuff that normal team members don't.
> One of the sensitive information that needs to be displayed and more
> importantly, stored in the database is the employee performance reviews
> (HR access only).
> I am wondering how I would go about storing/maintaining this
> information as even the DBA's are not supposed to have access to this
> information, but should still be able to administer it and/or retrieve
> the encryption keys if the HR personnel forget their passwords etc.
> The simplest way I can think of is to issue all the HR people with one
> password, encrypt the data in the DB layer (in code) and then store it
> in a table in the database. This way, anyone with access to the
> Database won't necessarily have access to the data. The problem with
> this one is that all the HR personnel share one password, which doesn't
> seem like a nice (secure) solution.
> The other option was to store the encryption key in the database and
> encrypt the key itself with the password provided by the individual
> user (HR person), which will be stored against that user's record. This
> way, whenever a data request is made, the database sends the encrypted
> performance review data and the password encrypted key to the user. The
> user will then decrypt the key using their password, and then decrypt
> the data using the key. This way, if the user ever forgets their
> password, all the DBA (and/or App with owner acess) has to do is
> reencrypt the key with a new user password and store it against the
> user's record. The problem with this is storing the key in the database
> and yet restricting access to the DBA. They should not have raw access
> to the key (which essentially means they can see the data in the
> database), but still should have enough access that if a user forgets
> their password, the DBA can reset the password without losing all the
> existing data.
> I was thinking of storing the key on a key server somewhere with
> different access rights etc. but that means the system starts to get
> complex.
> How would you guys go about building such a system? Does MS Sql Server
> 2005 provide any mechanisms for such functionality? Plus, as a design
> issue, is it better to let the database handle the encryption, key
> management, roles/privileges or is it better to do it in the
> application itself? One of the advantages I can think of for doing it
> in the application is that it gives a bit of database independence, i
> can change the underlying database easily without having to rewrite a
> whole heap of functionality. The other reason is that this way, i won't
> have unencrypted data or keys on wire.
> Looking forward to some expert opinions and discussions regarding this
> problem.
> Thanks in advance.|||The key (ahem) here is this:

Plain and simple. If the DBAs can read the decryption key, they can
read the data. So the statement "but should still be able to administer
it and/or retrieve the encryption keys if the HR personnel forget their
passwords etc." is what I call a "non-starter". By definition, the DBAs
have access to the data.
You're right on one point though. If all people share access to the
same password or decryption key, you have an insecure solution. Think
of it this way -- the bigger the "secret" is, and the more people who
have access to that secret, the less secure the system.
Encrypting the key doesn't work either because you have to encrypt the
key with another key. Where are you going to put that key? Are the
DBAs going to have access to it too?
Here's my take on it:
Carefully analyze what is and what is not a secret in the system. Only
encrypt the real secrets. Limit the access to the database to the
smallest set of people possible. If you don't want the DBAs to have
access to the encryption key, that by definition means you have to store
the key outside the database. I recommend that you use .NET's
encryption routines to encrypt your secrets *outside* of the database,
and simply store the data in the system once it's encrypted.
So it boils down to this. SOMEBODY needs access to the keys. They need
to exist somewhere (even if it's not on your SQL Server). The
administrator of that machine will probably be able to read the key.
Deny that person read access to the database. That person needs to be
trustworthy, too. If he/she is not, they can share the key with an
untrustworthy DBA and get your data by writing a small program.
HTH
-Dave
nyathancha@.hotmail.com wrote:[vbcol=seagreen]
> Hello? Anyone? Surely this is not such a hard problem, or particularly
> unusual. I would guess there are many times when you would want to
> encrypt the data in a database, so as to make it inaccessible to the
> DBA's, but still give them some administrative privileges. One of the
> constraints is that the user with access to the data doesn't
> necessarily "own" the data, so that if they forget the password (i.e
> lose the encryption key), all the data is not lost.
>
>
>
> On Jan 22, 6:37 pm, nyathan...@.hotmail.com wrote:
>
-Dave Markle
http://www.markleconsulting.com/blog|||Hi Dave, Thanks for the reply.
I was thinking along the same lines as well. I couldn't think of anyway
of storing the key in the database and still restricting access to the
DBA. Which is why I mentioned in my original post the "key server
somewhere with
different access rights" . Unless sql server has some magic
features/functionality that I don't know about.
So how hard would it be to build/deploy a key server? Maybe this is
more of an ecryption/security question rather than a database question.
My idea of encrypting the key with another key was that the second
"key" would be the user's password. This way the DBA doesn't have
access to the original data encryption key even if its stored in the
database (because it can only be decrypted using the user's password),
but if the user does forget their password, the data is not lost
because it wasn't encrypted with their password(/key). The DBA can
"reset" their password by reencrypting the orginal data key with a new
user password key and storing it against the column. Which is where I
came to the key server. The fact that the DBA should be able to get the
data key encrypted with a user password, but not the raw data key
itself. I can't think of anyway of doing this without an independent
application.
Does anyone know any commercial encryption key storage and distribution
solutions/products?
I am sure at some stage the data encryption key will itself have to be
encrypted before being put on the wire. Other wise you would have the
encrypted data coming from the database, the plain encryption key
coming from the key server. Doesn't seem particularly secure.
What is the general industry solution to a situation like this, where
you want to store sensitive data in a database, but the data is
accessible (i.e belongs to) to more than one person (so that if that
one person forgets their password/key, the data itself is not lost)
On Jan 25, 1:28 pm, Dave Markle <"dma[remove_ZZ]ZZrkle"@.gmail.dot.com>
wrote:
> The key (ahem) here is this:
>
> Plain and simple. If the DBAs can read the decryption key, they can
> read the data. So the statement "but should still be able to administer
> it and/or retrieve the encryption keys if the HR personnel forget their
> passwords etc." is what I call a "non-starter". By definition, the DBAs
> have access to the data.
> You're right on one point though. If all people share access to the
> same password or decryption key, you have an insecure solution. Think
> of it this way -- the bigger the "secret" is, and the more people who
> have access to that secret, the less secure the system.
> Encrypting the key doesn't work either because you have to encrypt the
> key with another key. Where are you going to put that key? Are the
> DBAs going to have access to it too?
> Here's my take on it:
> Carefully analyze what is and what is not a secret in the system. Only
> encrypt the real secrets. Limit the access to the database to the
> smallest set of people possible. If you don't want the DBAs to have
> access to the encryption key, that by definition means you have to store
> the key outside the database. I recommend that you use .NET's
> encryption routines to encrypt your secrets *outside* of the database,
> and simply store the data in the system once it's encrypted.
> So it boils down to this. SOMEBODY needs access to the keys. They need
> to exist somewhere (even if it's not on your SQL Server). The
> administrator of that machine will probably be able to read the key.
> Deny that person read access to the database. That person needs to be
> trustworthy, too. If he/she is not, they can share the key with an
> untrustworthy DBA and get your data by writing a small program.
> HTH
> -Dave
>
> nyathan...@.hotmail.com wrote:
>
>
>
>
>
>
>
>
> -Dave Markle
> http://www.markleconsulting.com/blog