Problem In Creating Asymmetric Keys

Aug 7, 2006

I am a novice to the SQL server. I am trying to create Asymmetric key using the query

CREATE ASYMMETRIC KEY PacificSales19 AUTHORIZATION dbo

FROM FILE = ' C: emp emp1.snk'

ENCRYPTION BY PASSWORD = 'ABC123!@#$';

GO

But I alwys get the follwing error

The certificate, asymmetric key, or private key file does not exist or has invalid format.

Can anyone please guide me as to how to go ablout creating the ASYMMETRIC KEY FROM FILE.

Thanks and regards

View 4 Replies

Symmetric Encryption By Asymmetric Keys

Nov 9, 2007

i've getting ready to implement encryption on a rather large database. I'd read that if performance is of utmost concert, you should use symmetric keys. I want to encrypt those keys by asymmetric keys. My code is working, but i'm just not sure if there is a quicker way? do you have to open and close the key each time you select/update/insert in a stored procedure that references an encrypted column, or is there a way to just modify the code by adding the encryptbykey/decryptbykey functions?

has anyone implemented encryption on columns in large tables? any suggestions for me?

Thanks,
Pete

here's my code to create the keys:

create asymmetric key ASK_Auto_Encrypt
with algorithm = RSA_512;

create symmetric key SK_AE
with algorithm = TRIPLE_DES
encryption by asymmetric key ASK_Auto_Encrypt;

here's my code to test this:

create table encryption_test (test varchar(50));

open symmetric key SK_AE
decryption by asymmetric key ASK_Auto_Encrypt;

insert into encryption_test
select encryptbykey(key_guid('SK_AE'),'test');

select convert(varchar(max),decryptbykey(test)) from encryption_test;

close symmetric key SK_AE;

View 14 Replies View Related

Creating Inter-table Relationships Using Primary Keys/Foreign Keys Problem

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

Creating Indexes On Columns That Are Foreign Keys To Primary Keys Of Other Tables

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

Script For Creating Table With Multiple Primary Keys

Feb 1, 2007

Hi,

I am trying to execute following sql script in sql-server 2000 query analyzer

CREATE TABLE user_courses (user_id varchar(30) NOT NULL PRIMARY KEY,
course_id varchar(10) NOT NULL PRIMARY KEY)

Its give's me following error :-
Cannot specify multiple primary key constraint

Hence I am not able to ceate table with multiple primary keys. So can any one tell me how to get this done?.

Secondly, Primary key must be unique i.e duplicate values are not allowed in P.K field. But in this case since I am declaring two fileds as primary keys.
Will it allow me to have following records in the user_courses table?
user_id(P.K) course_id(P.K)
bob CRS235
alice CRS235
Tim CRS235
tom CRS635

So, if we consider both the fields as primary keys together than I am not voilating Uniqueness constraint. But, if I look at course_id alone then I am voilating uniqeness property?

Thanks,

View 4 Replies View Related

SQL XML :: Shred To Relational Tables - Creating Foreign Keys

Oct 7, 2015

I'm shredding the below xml into relational tables. Each element of the xml has it's own table and there is a foreign key to join the tables, you can see this in the below picture. The process I follow is each relational table I always bring the nesecary xml and store it in the table and when shredding I always look at the parent table.So for example when processing the seat table, I use seat xml from the parent route table, also taking the ROUTEID from the route table. The reason I do this is all about taking the id from the previous step to create the relationships between the tables. without taking the xml down to the tables?The problem with this approach is I have xml stored in most tables and the tables are becoming very large.

<Route Type="OneWay" >
<Seat Type="FirstClass">
<Prices>
<Price Price="10" />
<Price Price="11" />
</Prices>

[code]....

View 4 Replies View Related

CREATE ASYMMETRIC KEY Issues.

Aug 8, 2006

Hi
I created an sqlserverproject successfuly ( just has one CLR stored proc)
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/mandataaccess.asp)
i followed above steps to create the project.
when i tried to deploy the same to sql server VS studio threw below error
CREATE ASSEMBLY for assembly 'MySqlServerProject' failed because assembly 'MySqlServerProject' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. MySqlServerProject
then i realized i will have to either sign the assembly or create an asymmetric key. i decied to follow the latter. so i tried below t-sql statements in sql sever 2005
use master
GO
CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'C:myWorkSQL2005DBProMySqlServerProjectMySqlServerProjectinDebugMySqlServerProject.dll'
when i execute the above, SQL Server threw below error
The certificate, asymmetric key, or private key file does not exist or has invalid format.
What i am i doing wrong. please advise
THNQDigital

View 2 Replies View Related

CREATE ASYMMETRIC KEY Issue

May 8, 2007

Hi,
I am trying to create an asymmetric key to have EXTERNAL ACCESS ASSEMBLY for an SQL login. When I try to run following script. it gives error
1 User master2 GO 3 CREATE ASYMMETRIC KEY SN FROM EXECUTABLE FILE = 'D:Partners.dll' 4 CREATE LOGIN TestLogin FROM ASYMMETRIC KEY SN 5 GRANT EXTERNAL ACCESS ASSEMBLY TO TestLogin6 GO7
Error is "The certificate, asymmetric key, or private key file does not exist or has invalid format."
Any help how can I fix this error. The database already have Trustworth ON.

Regards,

View 2 Replies View Related

How To Backup Asymmetric Key In SQL 2005

Feb 5, 2006

How
to backup asymmetric key in SQL 2005 created in the following way so it
can be copied to another server ? Also can you copy it to the other
server after backing it up.

CREATE ASYMMETRIC KEY ccnumber WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'password';

View 11 Replies View Related

Prolem With Create Login From Asymmetric Key

Feb 26, 2007

Greetings...

I'm trying to create assembly with "PERMISSION_SET = UNSAFE".
For that I've signed assembly's .dll and installed root certificate to ��Trusted Root Certificate Authority.�?: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx
now I'm trying to create login from asymmetric key:

USE master
GO

CREATE ASYMMETRIC KEY SQLCLRTestKey
FROM EXECUTABLE FILE = 'C:Documents and SettingsAll UsersDocumentshunterStoredProcedures.dll'
CREATE LOGIN SQLCLRTestLogin
FROM ASYMMETRIC KEY SQLCLRTestKey

but I'm receiving error: "Cannot find the asymmetric key 'SQLCLRTestKey', because it does not exist or you do not have permission."

What's wrong?

Best regards...

View 6 Replies View Related

SQL Server 2014 :: CLR Works With Trustworthy OFF And No Asymmetric Key

Apr 28, 2015

We have a curious situation on a SQL 2014 DB, with Trustworthy set to OFF. There is a job that runs a data export to a file via a CLR. The assembly as PERMISSION_SET = EXTERNAL_ACCESS, however there is no Asymmetric key for the assembly. Therefore what I trying to work out is why this is NOT failing. Some further information on this specific database that may or may not be relevant is:

1. It was upgraded a few weeks ago (Backup/Restore) from a SQL 2012 - SQL 2014 server
2. It as a Compatibility Level = 110 (2012)
3. The Previous 2012 database DID have Trustworthy ON
4. The CLR are actually being run against a snapshot of the database (Actually I think this one is a red herring. The SP is getting data from a table in the snapshot, but the CLR used it the one from the main DB)

View 2 Replies View Related

DB Engine :: What Is The Use Of Mapped To Certificate / Asymmetric Key / Credentials

Jul 23, 2015

1) what is the use of mapped to certificate/asymmetric key/credentials

2) how to use mapped to certificate/asymmetric key/credentials

3) in which conditions we can use mapped to certificate/asymmetric key/credentials which is present on login properties page

[URL]

View 3 Replies View Related

SQL Server 2012 :: Asymmetric Encryption Using Public And Private Key

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

Generate Script For Primary Keys And Foreing Keys

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

The Certificate, Asymmetric Key, Or Private Key File Does Not Exist Or Has Invalid Format.

Sep 20, 2006

I am sure I'm being dumb here but I am trying to deploy an assembly with external_access.

I have signed the assembly using the <new> option in the project properties.

When I then try and create the Key I get the above error using the code below.

CREATE ASYMMETRIC KEY SQLExtensionUDTKey

FROM EXECUTABLE FILE = 'C:Documents and SettingsSimon SabinMy DocumentsVisual Studio 2005ProjectsSQLBitsCoreSQLExtensionsSQLExtensions.UDTinDebugSQLExtensions.UDT.dll'

What could be the problem?

View 7 Replies View Related

Urgent !!!!! Nee Explanation On Primary Keys And FK Keys

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

Foreign Keys - On Which Kind Of Keys Do The Base On?

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

Auto Incremented Integer Primary Keys Vs Varchar Primary Keys

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

Login Failed For User 18456 When Using Asymmetric Key For Login

Dec 26, 2005

Hi,

I wanted to check the asymmetric key option in sql 2005. I copied the books online code for creating a asymmetric key and then used this key for creating a login. Now when I try to login without entering any password I am receiving 18456 error. I would like to know what I am missing here. If I use CREATE LOGIN from asymmetric key or certificate how do I login and with what credentials. Do I need to provide any password.CREATE ASYMMETRIC KEY PacificSales09
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = 'bmsA$dk7i82bv55foajsd9764';
GO

CREATE LOGIN asm FROM ASYMMETRIC KEY PacificSales09;

Regards,

Ravi

View 5 Replies View Related

Creating Trigger On Creating Table

Jan 28, 2008

Hi,

If I want to automatically insert a record which has default value in a table,
how can I create the trigger?

View 5 Replies View Related

Keys

May 24, 2004

hi
lets say i have table student(id,name) id =pk
table course(cno,cname)cn=pk
now iam a fresh graduate as i learned from uni if i want to get the couses that each student took i would make a table called studentcouse(id,cno) and put the two of them pk
now iam working and at work they told me to do so:studentcouse(studentcouse_serial,id,cno) studentcouse_serial=pk .but i told them that dublicate filed may occur amd they told me that we have a function that will remove dublicate.
so iam asking u if who is right me or them and if u can tell yr comments
thanx a lot

View 5 Replies View Related

Where Are My Keys?

Apr 24, 2000

Hi,
Can anybody of u guyz help me out of this. I transferred my tables(about 250) to another server using DTS. But all my keys got dropped. I tried using Replication and still the destination is without my keys. What i need to do get my keys? Do i need to take a script of my keys and execute it there in destination server? If so, everytime i need to do that? Please help me out in these problem..
I really appreciate any help amidst your precious schedule..

Thank you very much
Rose, Shania

View 3 Replies View Related

Primary Keys

Nov 15, 2006

"Violation of PRIMARY KEY of restriction 'PK_Approve_Overtime'. The overlapping key cannot be inserted in object 'Dbo.Approve_Overtime'. The statement was ended."
can soemone explain to me why i have this kind of error?
i have this two tables. approve_overtime table has a primary key id_no and application_input table with a primary key of id_no!
all the values from of application_input will be stored also in approve_overtime.
sometimes the datas can be stored.sometimes it cannot and produces an error!

what do u think?

hmmm pls help!

View 1 Replies View Related

Foreign Keys

Apr 17, 2007

Can any body tell me how to know to what columns of other table it refers to.
shiva kumar

View 2 Replies View Related

Secondary Keys

Apr 3, 2008

Hi,
I am currently playing with Sql Express 2005, and I am wondering if there is any way to create secondary keys on a table?
I know you can create compound Primary Keys (not sure that is the correct terminology), and Foregin Keys. However, I am unsure about secondary keys ( compound or otherwise).
Could someone tell me if this is possible. I am looking at migrating from an Acucobol Vision file system, which makes heavy use of secondary keys.
Cheers

View 5 Replies View Related

Keys And Indexes

May 26, 2008

i have some confusions with keys and indexes.. plz let me know whether the following are correct..- Every Primary Key is a Clustered Index- A Primary Key cannot exist without a Clustered Index- Every Unique Key is a Non-Clustered Index - Non-Clustered Index is the DEFAULT Index- A table can have only 1 Primary key- A table can have only 1 Clustered Index- A table can have any number of Unique Keys- A table can have any number of Non-Clustered Indexes

View 5 Replies View Related

Primary Keys

May 16, 2002

Using SQL Svr 7.0. It appears that primary keys are created as nonclustered
unique indexes. Is there a configuration setting I can use to make them be
created as clustered unique indexes?

View 1 Replies View Related

Primary Keys

Apr 4, 2001

If a table has a column defined as Int, Identity(1,1) which is to be used as the primary key, should that index be defined as clustered or non-clustered? In Enterprise manager when you create a PK on a table it defaults to being a clustered index. I am sure the answer depends on the other index requirements and columns in the table but I'd like to see what other ppl think about this.

Thanks!

View 4 Replies View Related

Two Primary Keys???

Mar 14, 2000

I have read that SQL Server tables can't have more than one primary key. I know in Access two keys are allowed. Why can't there be two primary keys in a single table in SQL Server 7.

Thanks

View 1 Replies View Related

Foreign Keys

Aug 24, 1999

Could someone enlighten me as to the advantage of using the foreign key tab when in table design mode in the Enterprise Manager. Does it have any advantages ?? Is it necessary ??

thanks in advance

Paul

View 1 Replies View Related

Foreign Keys

Mar 29, 2001

I haven't tried, but does anyone know if its possible to a have a foreign key for two tables when the tables reside in different databases (on the same server)?

Thanks,
Doug Smith

View 1 Replies View Related

Foreign Keys

Oct 25, 2001

l'm trying to do inserts on tables with foreign keys and they keep crashing. Can somebody please help.Whats the best way of populating data that has foreign keys?

View 1 Replies View Related

Primary Keys

Jun 15, 2004

Hi everyone,
Does someone knows how can I drop a primary key (that I don't know the name) from a table in one sql statement.

Thanks,
Fady

View 3 Replies View Related