Using SQL Server 2005 Public Keys In VB.Net
May 1, 2006
I'm creating an asymmetric key in SQL Server:
CREATE ASYMMETRIC KEY MY_KEY
WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'password1@'
I'm then taking the public key...
SELECT public_key
FROM sys.asymmetric_keys
WHERE [name] = 'MY_KEY'
I'm trying to import this into VB.Net using RSACryptoServiceProvider.
Dim RSA As New
Security.Cryptography.RSACryptoServiceProvider(512)
RSA.ImportCspBlob(KeyBlobByteArray)
I have to add the following line to get it to not give me errors.
(Why?) But the data looks right when I look at the exponet and modulus
of the parameters that are exported and then immediately imported.
Something is obviously being thrown away when I do this, but I don't
know what. It's probably the key to the whole problem.
RSA.ImportParameters(RSA.ExportParameters(False))
I encrypt the message...
EncryptedMessageByteArray = RSA.Encrypt(MessageByteArray,
False)
And in SQL server I try to decrypt the message and I get NULL.
SET @DecryptedMessage =
DecryptByAsymKey (
AsymKey_ID('MY_KEY') ,
@EncryptedMessage ,
N'password1@')
I've noticed that the encrypted array is completely different in VB
than when I encrypt it in SQL (obviously), but I can't figure out how
to configure to the RSACryptoServiceProvider to match what SQL Server
is doing under the hood.
Any ideas?
Thanks,
Jerrill
View 10 Replies
ADVERTISEMENT
Jul 30, 2007
Here is what I am trying to do. I want to create a private / public key pair (either a certificate or asymmetric key) and be able to give the public key to my business contacts. They would then use the public key to encrypt data to be sent to me and then I could import that data into my SQL 2005 database and use the private key to decrypt it.
It seems to me that this should be a pretty simple process, but it is not. The reason I want to do this is that the data they send to me on a regular basis is sensitive. Currently we use PGP, but then I have to decrypt the data and then import it and then reencrypt it. It would greatly simplify my process if I could just import the data and use it.
So how can I do this. Most of the posts I read say that asymmetric keys are avaiable in SQL2005 but not recommended for use. I don't want my business contacts to be able to decrypt any of my data (if they got access to the database) just encrypt.
Any recommendations???
Thanks!
Jim Youmans
St Louis, Missouri
View 5 Replies
View Related
Apr 19, 2006
SQL Server Service 2005 Pack 1 is available here.
The list of new features and fixed was published here on KB Article 916940 .
Keep in mind to read the readme file first in order to avoid problems during the update or unexpected bahaviour.
Have Fun !
-Jens Suessmeyer.
View 6 Replies
View Related
Oct 7, 2007
I've been trying to re-install VS2005 & MS SQL Server express, and I've hit a snag trying to install SQL Server express.
I started by trying to use the automatic uninstaller for VS and had problems so I tried manually uninstalling it (bad idea), which didn't work either, so I re-installed VS completely, then uninstalled it using the automatic uninstaller. I then tried installing, which worked for VS2005, but it didn't work for SQL Server. I've tried 3 times now, and each time it says SQL Server 2005 failed to install.
I checked the summary.txt log found in C:Program FilesMicrosoft SQL Server90Setup BootstrapLOG and from what I understand, it seems that I don't have priveledges to change some registry keys. This has me really lost because I'm installing as Administrator, as you can see from the log below, so I should have access to the registry. Administrator is the only account on the computer so I know I haven't messed up priveledges from another account.
I've included the contents of the summary log below if anyone can make any sense of it and has any ideas I can try.
Code Block
Microsoft SQL Server 2005 9.00.3042.00
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Sun Oct 07 10:12:41 2007
MRAWESOME : To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.
Machine : MRAWESOME
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.3042.00
Install : Successful
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0004_MRAWESOME_SQLSupport_1.log
--------------------------------------------------------------------------------
Machine : MRAWESOME
Product : Microsoft SQL Server Native Client
Product Version : 9.00.3042.00
Install : Successful
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0004_MRAWESOME_SQLNCLI_1.log
--------------------------------------------------------------------------------
Machine : MRAWESOME
Product : Microsoft SQL Server VSS Writer
Product Version : 9.00.3042.00
Install : Successful
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0004_MRAWESOME_SqlWriter_1.log
--------------------------------------------------------------------------------
Machine : MRAWESOME
Product : MSXML 6.0 Parser (KB933579)
Product Version : 6.10.1200.0
Install : Successful
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0004_MRAWESOME_MSXML6_1.log
--------------------------------------------------------------------------------
Machine : MRAWESOME
Product : SQL Server Database Services
Error : SQL Server Setup failed to modify security permissions on registry key SOFTWAREMicrosoftMicrosoft SQL Server90Replication for user Administrator. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that the registry key exists on the destination drive.
--------------------------------------------------------------------------------
Machine : MRAWESOME
Product : SQL Server Database Services
Error : SQL Server Setup failed to modify security permissions on registry key SOFTWAREMicrosoftMicrosoft SQL Server90Replication for user Administrator. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that the registry key exists on the destination drive.
--------------------------------------------------------------------------------
Machine : MRAWESOME
Product : Microsoft SQL Server 2005 Express Edition
Product Version : 9.00.1399.06
Install : Failed
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0004_MRAWESOME_SQL.log
Last Action : InstallFinalize
Error String : SQL Server Setup failed to modify security permissions on registry key SOFTWAREMicrosoftMicrosoft SQL Server90Replication for user Administrator. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that the registry key exists on the destination drive.
Error Number : 29508
--------------------------------------------------------------------------------
SQL Server Setup failed. For more information, review the Setup log file in %ProgramFiles%Microsoft SQL Server90Setup BootstrapLOGSummary.txt.
Time : Sun Oct 07 10:18:00 2007
List of log files:
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0004_MRAWESOME_Core(Patched).log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0004_MRAWESOME_SQLSupport_1.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0004_MRAWESOME_SQLNCLI_1.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0004_MRAWESOME_SqlWriter_1.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0004_MRAWESOME_MSXML6_1.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0004_MRAWESOME_SQL.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0004_MRAWESOME_Datastore.xml
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0004_MRAWESOME_.NET Framework 2.0.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0004_MRAWESOME_Support.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0004_MRAWESOME_Core.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGSummary.txt
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0004_MRAWESOME_SCC.log
View 1 Replies
View Related
Feb 14, 2008
set up windows 2003 server
sql server 2000
have public IP from netword soluctions
I can see website
what settings to i need to open up sql server to the public?
View 12 Replies
View Related
Mar 30, 2006
In exploring permissions that users have, I find that they all have VIEW ANY DATABASE permission which they inherit from the public server role. You can see this by selecting the Permissions page on the Server Properties dialog and highlighting "public". The permission shows as having been granted by sa. This is listed as a server role. However, it does not show in the list of server roles and I can't find any documentation for it (RTM BOL). Interestingly, if I revoke this permission (which is the only permission this role has), the public server role disappears from view. But I can subsequently regrant the permission with Transact-SQL in master and the role comes back.
I would like more information about this role. It seems to be sort of "secret".
When I revoke the permission, users can't see any databases except master and tempdb (both of which have active guest users) even though they have been granted access to other databases.
What I was trying to accomplish by changing this permission was to allow a user to see only those databases which they are allowed to use. But that does not seem to be possible.
Thanks for the help.
Sharon
View 5 Replies
View Related
Feb 14, 2008
set up windows 2003 serversql server 2000have public IP from netword soluctionsI can see websitewhat settings to i need to open up sql server to the public?
View 1 Replies
View Related
Sep 21, 2005
I am working with a SQLServer installation where all public permissionshave been revoked from the system.I currently hold the securityadmin and sysadmin roles to perform mylogicall access control work (creating logins and adding users todatabases and changing group memberships.)There has been a question as to whether or not I need the sysadmin roleto do my job, so we tried an experiment in dropping the sysadmin rolefrom my id.With no public permissions, I see no user information on the server,which really limits my ability to do the job.Has anyone ever worked with a super-locked-down server without thesysadmin role for doing security admin work?What I'm looking for is hopefully SQL to perform the access privilegesneeded for the security admin role by itself.Thanks in advance!
View 2 Replies
View Related
Feb 21, 2007
I need a master view that gets data from different DBs located on the same SQL Server (similar with Oracle feature that allows to get data from different schemas).
Is it possible to create such a view ?
Thanks
View 5 Replies
View Related
Oct 17, 2007
Hello,
Does anybody have the transact SQL to find the permissions granted to the public role in a Database ?
Also looking for the SQL to find the permissions granted to the user Guest in a database ?
thanks
View 5 Replies
View Related
Jun 3, 2015
We are planning to encrypt few fields using asymmetric encryption. Tyring share public key with users and retain private key with us. How to generate keys? Haven't found any solid document on how to generate these keys.
View 0 Replies
View Related
Apr 11, 2006
Hello again,
I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.
Here's the query for my Classes table:
Code:
CREATE TABLE Classes
(
class_id
INT
IDENTITY
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
class_title
VARCHAR(50)
NOT NULL,
class_grade
SMALLINT
NOT NULL
DEFAULT 6,
class_tardies
SMALLINT
NOT NULL
DEFAULT 0,
class_absences
SMALLINT
NOT NULL
DEFAULT 0,
CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)
This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:
Code:
CREATE TABLE Classes_have_Grades
(
class_id
INT
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
grade_id
INT
NOT NULL,
CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),
CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)
Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?
In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?
Thank you for your assistance.
View 1 Replies
View Related
Jul 16, 2014
what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:
[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------
The foreign key above is as:
ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])
What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:
CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)
In other words, what's best practice for adding an index which best supports a Foreign Key constraint?
View 4 Replies
View Related
May 16, 2008
Pls let me know How I generate script for All primary keys and foreign keys in a table. Thereafter that can be used to add primary keys and foreign keys in another databse with same structure.
Also how I script default and other constraints of a table?
View 2 Replies
View Related
May 29, 2006
Hi There
We have been playing around with encryption in 2005. I cannot find a BOL topic that discusses dropping encryption objects such as keys.
We do the followign steps:
Create master key with password, then we create a certificate using the master key, we then create a symmetric key using this certificate and encrypt data columns.
But what i find worrying is that you can then drop the symmetric key , there are no warnings that you have objects dependant on this key for decryption.
Once you have dropped the key you cannot decrypt the data anymore?
Also the key defults the expiration date to 1 year.
WHat happens after 1 year when you have encrypted data and an expired key, or someone drops the key ? How can you ever decrypt the data after that ?
You can backup master keys nd certificates but not symmetric keys?
It seems to be that youc an very easily orphan encrypted data by the loss of the symmetric key for whatever reason, is this correct ?
Thanx
View 6 Replies
View Related
Feb 14, 2007
I have a question about the storage of symmetric keys in SQL Server 2005 due to the fact that I have read two conflicting statements on this.
In Laurentiu's blog located at http://blogs.msdn.com/lcris/archive/2005/10/14/481434.aspx, in regards to preventing symmetric key loss he makes the statement that "...Because the keys are stored in the database, they will be saved with the database....".
But in the white paper Improving Data Security by Using SQL Server 2005, which is located at http://www.microsoft.com/technet/itshowcase/content/sqldatsec.mspx, in regards to symmetric keys the statement is made "...Note: The symmetric key is not stored in the database. Only the encrypted values of the symmetric key are stored in the database. Therefore, users who can access the database cannot decrypt the data without first decrypting the symmetric key....".
So I am just wondering which statement is correct, are symmetric keys stored in the database or not?
Thanks!
Ginny
View 1 Replies
View Related
Jul 15, 2002
Can somebody explain to me how to best do inserts where you have primary keys and foreign keys.l'm battling.
Is there an article on primary keys/Pk ?
View 1 Replies
View Related
Nov 22, 2007
Hello!I have a table A with fields id,startdate and other fields. id and startdateare in the primary key.In the table B I want to introduce a Foreign key to field id of table A.Is this possible? If yes, which kind of key I have to build in table A?Thx in advance,Fritz
View 6 Replies
View Related
Mar 25, 2008
Hi everyone, I am trying to upgrade a 2000 DB to 2005 with SSIS Database Transfer.
I finally got it to work with an online transfer but the destination database has no primary keys, indexes or identity columns from the source DB.
What am I missing?
What other ways are there to upgrade a SS2000 DB to 2005?
Thank you.
View 4 Replies
View Related
May 4, 2006
I know my problem is not directly associated w/ SSIS, so please forgive the post here. I figured this would be the "most" appropriate place to post this challenge.
We're attempting to edit a 2000 DTS package in Design mode from within the 2005 SQL Management Studio. To do this we downloaded/installed the following packages from the MS download site:
Microsoft SQL Server 2000 DTS Designer Components
Microsoft SQL Server 2005 Backward Compatibility Components
We're able to open the package in design mode from the Management Studio (Server > Management > Legacy > Data Transformation Services > DTS Package Name). But, when the DTS designer is open, the backspace and arrow keys won't respond in the Management Studio. They begin responding when we close the DTS designer. There are no error messages when we close the DTS designer.
Does anyone know of a fix or a workaround? Any help would be greatly appreciated.
Thanks, Mike
View 7 Replies
View Related
Aug 13, 2007
Hi,
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Regards
Mike
View 7 Replies
View Related
Jan 12, 2005
In SQL Server 2000, I'm looking for input as to how to set up primary keys (PKs).
Is it safe to use the IDENTITY feature using AUTOINCREMENT as a setting for a PK, or is it best to generate my own and check for dups?
My concern is whether SQL Server at any point will taint the numbers used as the PK -- because as you know, these PKs are going to wind up linking into (relating into) other tables (e.g. client orders).
Certainly it's vital that the clientID not get lost/changed during any regular DB maintenance or use.
Any input would be appreciated.
View 1 Replies
View Related
Oct 26, 2005
Hi,
The way i generate my primary keys is to keep a table
PrimaryKeyTable
PrimaryKeyName : varchar (name of the primary key, for example UserID)
PrimaryKeyID : int (the next key should have the number stored in that
field, if it was 12 then next time i add a row the UserID will be 12)
After adding a row, i increment the value of primaryKeyID.
Is there a way to let SQL server to handle the primary key? To let SQL generate the key himself instead of me?
Thanks,
View 4 Replies
View Related
Feb 14, 2006
I am getting the following error in the database..
B-tree chain linkage mismatch
Keys out of order on page
I have run the DBCC Repair_rebuild with data_loss.
But this has not helped to resolve the issue. It is finding errors in the Primary Key Linkage.
Please assist.
Thanks for your help.
Karthik
View 20 Replies
View Related
Mar 27, 2007
As part of a project, I'm not allowed to use foreign keys, well can't. But I have a problem with this master detail relationship (and a very simple one too).
Tables to begin with:
Invoice:
invoiceid : bigint indentity autoincrement primarykey
Detail:
invoiceid: bigint - detailnumber:bigint - desc : char20
Okay - I want to set these two tables up such than when I create a new invoice, and subsequently get the new invoiceid, and I start to add detail records to the detail table, I want the detailnumber to autoincrement when I do an SQL Insert. Right now I have detailnumber as the primary key, so when you view the detail records, it looks like:
(invoiceid, detailnumber, desc) 1,1,text - 1,2,text - 1,3,text - 2,4, text on another invoice - 3,5, text
I want it to work like:
(invoiceid, detailnumber, desc) 1,1,text - 1,2,text - 1,3,text - 2,1, text on another invoice - 3,1, text
where the invoiceid and the detail number make up the primary key.
I seem to recall you could do this is PHP before it had foreign keys & such, and I'm completely drawing a blank on this. BTW - I'm using enterprise manager to set this up, but I can use QA with a sample script to acheive the same.
I realize this may be (and probably is) off-topic here, but I can't figure out where to ask.
Thanks in advance.
View 3 Replies
View Related
Jan 25, 2008
I think I did this before but how do you designate foreign keys in SQL Server Management Studio?
View 2 Replies
View Related
Apr 19, 2007
When I install SQL Express on my XP Machine, the registry keys under local machine (ie, MSSQL.1, etc) show up, but on my Vista 64bit machine, the MSSQL.1 etc keys are not there. SQL Server still works, but the reg keys are missing.
View 1 Replies
View Related
Jan 30, 2007
Hello. I have a problem that spans VB.net, SQL Server and SSIS but is rooted in the need to encrypt column data in SQL Server.
I would like to encrypt data that I am bringing into SQL Server in the Data transformation script component of an SSIS package. I have achieved this but I can't decrypt the data because the keys don't match. I would like to use symmetric key encryption but I don't see how to get the symmetric key that I created in SQL Server available to the VB.net script component in SSIS.
Please advise me if my approach is correct and what steps I need to take.
View 5 Replies
View Related
Sep 13, 2007
Hi,
There alot of talk about Service Master and DB Master keys and the importance of backing them up seperately.
What about the DPAPI keys used to protect the Service Master keys. What procedures to I take to back those up during my database backup.
If I understand correctly, if I do a backup on one machine, and then try to restore in a different machine, I won't be able to decrypt data becuase the DPAPI keys don't "match".
This is assumming I don't use a password to protect my DB Master keys, I just rely on the service master to protect them.
Thanks for any help,
View 3 Replies
View Related
May 13, 2002
I am new at this and we encountered a problem. Can names in the public role be deleted? We have some names that need to go - however the delete option does not high light?
I would appreciate your help..
View 8 Replies
View Related
May 14, 2008
Hi Guys,
I am unable to deny DMV rights to public. I have already ran the SQL query successfully:
"DENY VIEW SERVER STATE TO public" and "DENY VIEW DATABASE STATE TO public"
However when I check my master DB, the public still have rights to all the dm_***** objects. Am I doing it wrong or is there any steps I missed out? Can anyone help please?
Thanks a million.
View 1 Replies
View Related
May 7, 2008
Is there a way to make a report public, to outside your domain users? We have a web application the users are authenticated in that is not in .NET and not using NT authentication and we want to have a report linked from inside the application that will pass the parameters in the URL. This is not very sensitive data and the report is using SQL authentication. We also don't want then to log in a second time for the report.
Can this be done easily?
Thanks
Linda
View 6 Replies
View Related
Feb 23, 2007
Hi,
I'm actually using global public variables in the custom code of my reports.
Is there a "chance" that , if the report is executed at the same time by two users, values calculated for an user will be crushed by other user's execution ?
Thanks in advance.
Grégory.
View 4 Replies
View Related