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.
I'm having a great deal of difficultyconnecting to SQL 2005 sp1 during the running of the deployment tool on the FE.. The error message indicates that I have to install the Backward compatible tool on the FE... Did that and upgraded SQL to SP1... Still a no-workie..Exactly what SQLs are compatible with the Public version of OCS... It worked fine with the Private version and SQL2005...
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.
I have some doubt about the IP Required for configuring cluster(both Window and SQL server)
Details : ---------- How Many IP(Public & Private) Required for 2 Node Active/Passive both window and SQL server Cluster ?
IP(Public & Private) Required : --------------------------------------- 1 : For Window Clustering : 2 node Active/Passive Cluster ? How many ip ? 2 : For Sql Server Clustering : 2 node Active/Passive Cluster ?how many ip ? 3 : Is there any mathematics for that ? How to analyse the IP requirement ?
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.
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.
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,
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,
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?
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?
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?
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
I am trying to setup service broker by following the article on http://support.microsoft.com/default.aspx/kb/915852
The script ran without error, but when I try to send a message, I get the following in the trace Audit Broker Conversation and the message is not delivered.
The certificate's private key cannot be found
This message could not be delivered because the security context could not be retrieved.
Does anyone know what I did wrong, is it the Certficate in the Master Database or the Target Database
i'm trying to make private messages like the one in this site, i didnt start programming yet, the problem is in the DB schema, i have 3 tables (Users, Messages, MessageDetails) i think the problem that the UserID is related to the other two Tables, so to know the sender and reciever, when i try to view all messages for specific user and show all users who sent it, it give nothing back, so any help in the DB, thanx 4 help.
One of our clients controls data for about 150 companies. Each company has the same schema and is running SQL Server 7. The maintenence task is horrible. He would like to put all the data in one large database, but needs to control the access, so a user at one company selecting data in a table cannot have access to another company's data in the same table. I thought about using views for each company but that is 150 companies times 100 tables. With Oracle8i you can make virtual private databases. Can this be done in SQL Server 7? Thanks in advance
I set up Service broker (2008R2 -> 2012) across different servers and domains using certificates.I set up 2 queues (one for sending, one for receiving).I set up 6 services (3 for send, 3 for receive), all 3 sharing the same queue type. (srv_send_1, srv_send_2, srv_send_3), (srv_receive_1, srv_receive_2, srv_receive_3).I set up a route for each receiving service on the source, and for each sending service on the target.I set up a SB binding to the remote for each sender service type.I granted send rights to the sending services on the source server.
I then tested my first service, and it worked perfectly (still works).My second service however is failing with the error "The certificate's private key cannot be found".I am stumped, as all the setup code was a lot of copy/paste which I have checked, rechecked and even re-coded.Basically, here is what Im doing:
begin dialog @ConversationHandle FROM Service srv_send_1 TO Service 'srv_receive_1' ON CONTRACT myContract WITH ENCRYPTION = OFF, LIFETIME = 60*60*24*7; SEND ON CONVERSATION @conversationHandle MESSAGE TYPE [my_msgtype] (@xml);
The above code works 100% with service1, but not with service2.Note that on profiler, the target server doesnt receive any communication. The error shows on the sender profiler.Also, The conversation shows up as CONVERSING in sys.conversation_endpoints and the entry exists in sys.transmission_queue.
Web server: win2003 server with iis6DB: sql server 2000I have 50+ remote offices running an Access 2002 app which connectsdirectly to sql server at a 3rd party hosting company, in part using anodbc connection on the workstation.We recently moved our database (and our web site) in-house. The newdatabase is inside the firewall but outside the dmz, where it livesalong side the company's most sensitive databases. IT's policy won'tallow us to connect directly from the internet. I'm trying to avoidbuying another copy of sql server for the Access app, which I one dayhope to drop anyway in favor ofThe web server uses port 1433 to talk to the database. I was wonderingif there is a simple way to route requests from the Access app throughthe web server.Thanks,Bob
I have a couple of questions on the nature of shared and private deployment of SQL Server CE 2005 on mobile devices:
1. I've read that with a shared install, SQL Server 2005 Compact Edition files are automatically updated and serviced by Microsoft Update. I take it that this only applies to installations on desktop computers -- if you're installing on mobile devices, there IS no automatic update. Right?
2. I've also read that in order to do a shared install of SQL Server 2005 CE, the user must have administrative rights. How does this apply to installs on a mobile device? There is no such thing as an administrative user on a mobile device. However, mobile devices CAN require different levels of privilege to install certain programs -- do you need something like an application signed for privileged execution (via a M2M certificate) in order to do a shared install of SQL Server 2005 CE on a mobile device?
Or is the shared versus private deployment only an issue when deploying to a desktop?
MSDN has examples of deploying SQLCE privately for C# and VB.NET, but that doesn't seem to translate to C++. A .NET application just loads the assembly it needs from the local directory, but from what I can tell I need to use the OLE DB driver to use SQL CE in C++. The problem is that the OLE DB driver is a COM object that (presumably) needs to be registered.
Has anyone successfully used SQLCE from C++ without registering any COM objects? I'm thinking the only way to do that would be to call DllGetObject() on the DLL and get the object manually, but I've never done this so I'd hate to waste the time if there's an easier way, or if this won't work.
We are looking to put column level encryption on a table. Great! Wonderful! Look in books online and see that you must give "control" perm to the Certificate for any user that needs to use the symmetric key.
Now my question. I have backed up the Certificate to file. What I have found playing around with the commands is it is possible to run the following command:
Alter Certificate MyCert Remove private key
BOL says their is no "Restore Certificate" command and to just use "Create Certificate". I have tried this and I keep getting an error saying the certificate already exist in the database.
What have I missed? How can I restore this certificate without restoring the ENTIRE database? Is there a permission set that can be applied to the Certificate to stop thsi kind of command from being run while still having the symmetric key usable? Any help would be of great use!
Thanks,
*note* this is using the MasterKey->Cert->SymmetricKey
Is there any harm in defining a SqlConnection as a private class variable for a web page?Private SqlConnection cn = new SqlConnectection(ConfigurationManager.ConnectionStrings["MyString"].ConectionString);The connection is only opened in certain event handlers, and is closed right after use. It was always my understanding that creating the connection is not a big deal but opening the connection is. Is that correct?
I'm working on a VB.Net 2005 application that uses SQLCE as a backend database.
Following the instructions on how to do a private installation (http://msdn2.microsoft.com/en-us/library/bb219482.aspx) so that admin rights were not needed, I successfully got it working as a ClickOnce deployment. But today during a demo for a client, they ran into this error while installing the latest version from our publishing URL:
"Unable to install or run the application. Application requires that assembly System.Data.SQLServerCe version 9.0.42 .... must be installed in the Global Assembly Cache."
Basically it's saying the user isn't an admin so they can't install. This is the problem I went to great lengths to try to avoid, and is the reason we are using SQL CE in the first place: admin rights are not required to install our application's database. To our knowledge, SQL CE is the only Microsoft product that fits this scenario.
Now, I haven't changed anything in the publishing of the application to my knowledge, and when I check the project prerequisites, the SQLCE engine still isn't in there (as summarized at the above URL). Again following the instructions at the URL above for private installation, the required assemblies are still part of the project's files.
It must be something I did to cause this, but I have no memory of changing anything in regards to this part of the application. Our deadline is in 4 days and I cannot continue development until I get past this issue.
Where do I look to fix this problem? A little help would be greatly appreciated.
Folks- By reading all the results I got from doing a search on "private install" in this forum, I thought this must be as easy as:
1. Copying the seven DLL's comprising the engine to my application's install dir on the deployment machine. If my app is installed at C:MyApp then CE dll's would go into C:MyApp, thats where my WinMain'd exe is residing. 2. RegSvr32 on sqlceca35.dll, sqlceoledb35.dll
Since I'm using native C++/OLE DB, I don't think I'd need to care about System.Data.SqlServerCe.dll
However my app which is basically the NorthWnd sample is failing on CoCreateInstance(CLSID_SQLSERVERCE_3_0............)
Which means oledb provider not registered on the machine? am I correct?
I watched Steve Lasker's video, even though the private install covers managed code, but I thought I should be doing something very similar, apparently I'm missing something.
I'll appreciate any help.
P.S., The 7 dll's above I mentioned are: sqlceca35.dll sqlcecompact35.dll sqlceer35EN.dll sqlceme35.dll sqlceoledb35.dll sqlceqp35.dll sqlcese35.dll
I just upgraded my SQL 2000 server to SQL2005. I forked out all that money, and now it takes 4~5 seconds for a webpage to load. You can see for yourself. It's pathetic. When I ran SQL2000, i was getting instant results on any webpage. I can't find any tool to optimize the tables or databases. And when I used caused SQL Server to use 100% cpu and 500+MB of ram. I can't have this.Can anyone give me some tips as to why SQL 2005 is so slow?
Hi Guys, I've got a bit stuck trying to decide on a table structure for a private message like part of a site I'm building (I'm using SqlServer 2000, not that it really matters).at first I was going to use a single table with the following schema: MessageId intReplyToMessageId int (nullable)FromUserId uniqueidentifier (I'm using .net membership)ToUserId uniqueidentifier IsRead bitIsDeleted bitSubject nchar(50)Body nvarchar(1000) DateCreated datetimeDateUpdated datetimeCreatedBy uniqueidentifierModifiedBy uniqueidentifier What strikes me here is that I'm using alot of guids (which are huge) and if I'm going to be threading the messages (which I am) there's no easy way to return an ordered list of messages that are replies to each other. So then I thought about introducting a messageThreads table, but I can't think of anything to store there apart from a threadid (which seems to defeat the purpose)Any ideas? I can clarify more if necessary. Thanks
The principle, partner and witnesss have two NICs each (NIC1 and NIC2). I want them to communicate in NIC2 for sending logs and establishing quorum. This will happen in their own private network (say 192.168.1.0/24). The NIC1 in each server will be available for client communication. The domain and clients are in the network (say 10.1.1.0/24).
I am using the same domain account as SQL server service account in all three servers.
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.
I've just bought a Virtual Private Server Account because I want to host a number of different ASP.NET sites, each backed by their own SQL database. I've downloaded SQLEXPRE.EXE and SQLServer2005_SSMEE-x64.msi to the server and installed the database engine and Management Studio Express on the VPS. My problem is that I cannot access the SQLEXPRESS on my server from my local machine. The Management Studio Express cannot get logged in. I've used "Surface Area Configuration Manager" to allow "Local and remote connections" using TCP/IP only. I've used SQL Server Configuration Manager" to enable the TCP/IP protocol. After making these changes I've stopped and started the SQL server - and even rebooted the machine. I've created a login "Remote User" for this access. I've added sqlservr.exe as an exception to the firewall. What else do I need to do? Is there a simpleton's guide to completing this task?
Hi, I am using express database and tools. I have an aspnetdb express database because I am using the login controls, personalisation...Can I use this database for my private tables also? Or do I need a separate express database for my private tables? Will using aspnetdb database, which contains special asp.net tables, for my private website tables create any kinds of problems in using my website? I want to use a single express database for my website, not two different express databases. Regards, Sandy
I am trying to create an assembly object when a report is initialized and I am getting the error:
[rsCompilerErrorInExpression] The BackgroundColor expression for the textbox 'textbox2' contains an error: [BC30390] 'ReportExprHostImpl.CustomCodeProxy.X' is not accessible in this context because it is 'Private'
In the Code tab, I have: Dim X As namespace.classname Protected Overrides Sub OnInit() X = new namespace.classname() End Sub
In my color field, I have "=code.X.color" If I replace the color field with "=namespace.classname.color" and have color be a shared property, then it works. However, I ultimately need the color to be based on individual user settings, so I cannot use the shared property approach.
Any ideas on how to get around this "because it is private" error?
FYI, I am trying to replicate the concept of themes using report formatting information pulled from a database.
I am trying to convert a certificate that was exported from our database server to be used by SQL Server for database encryption. When I run the PVKConverter, not Private Key File (PVK) is generated.The certificate has Server and Client Authentication as the purposes of the certificate.
What purpose or purposes does the certificate need in order to be able to be used by SQL Server 2012 SP2?Why doesn't the PVKConverter generate a private key file?I can use the command makecerts to generate a self signed certificate and have it work with SQL Server database encryption.
I'm trying to implement a SSAS Project in a Virtual Machine using a private network. When I try to deploy the solution the program gives me the following error:
I already have a Windows Authentication but still gives me that error.
