Create Certificate From Byte[]
Hi,
Just wondering if anyone knows if you can create a certificate from a byte[]. For example, you can create an assembly using CREATE ASSEMBLY FROM 0x...; specifying the hex representation of it - can you do the same with a certificate? This means that you don't need to save the file to disk before loading it into the database.
Cheers,
Adam
View Complete Forum Thread with Replies
Related Forum Messages:
Help With Error 'value Of Type Byte Canot Be Converted To 1 Dimensional Array Of Byte'
hi i am getting an error with my code, it says 'value of type byte canot be converted to 1 dimensional array of byte' do you know why and how i can correct this error, the follwoing is my code. can anyone help me correct the error and let me know ow to solve it thanks for any help givenPublic Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequestDim myConnection As New Data.SqlClient.SqlConnection("ConnectionString") myConnection.Open() Dim sql As String = "Select Image_Content from ImageGallery where Img_Id=@ImageId"Dim cmd As New Data.SqlClient.SqlCommand(sql, myConnection)cmd.Parameters.Add("@imgID", Data.SqlDbType.Int).Value = context.Request.QueryString("id") cmd.Prepare()Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader() dr.Read() context.Response.ContentType = dr("imgType").ToString()context.Response.BinaryWrite(CByte(dr("imgData"))) ----- this is the line with the error End Sub
View Replies !
Create Certificate
hey, I am having a weired issue(donno whether its weired or not.). I have a user who has db_owner rights on a database. But when he is trying to create a certificate he is getting error. "Msg 15247, Level 16, State 1, Line 1 User does not have permission to perform this action." Remember he is having db_owner rights on that particular database. is there any other permission that i have to give him.
View Replies !
Create A Certificate From Hex String
Hi, Does anyone know if there's a way to create a certificate from a hex string rather than a backup file? i.e: Code Snippet create certificate from 0x123ABC...; rather than create certificate from 'C:mycert.cer' (or whatever the syntax is) I know that you can do this for assemblies (and in fact I use this) - is there an equivalent for certificates? If not, why not? Cheers, Adam
View Replies !
Create User From Certificate Problem
Greetings... I'm trying to create a user (from certificate): USE master GO CREATE CERTIFICATE UnsafeSample_Certificate ENCRYPTION BY PASSWORD = 'All you need is love' WITH SUBJECT = 'Certificate for example_sp', START_DATE = '20070201', EXPIRY_DATE = '21000101'; BACKUP CERTIFICATE UnsafeSample_Certificate TO FILE = 'C:Documents and SettingsAll UsersDocumentshunterSampleCert.cer' WITH PRIVATE KEY (FILE = 'C:Documents and SettingsAll UsersDocumentshunterSampleCert.pvk', ENCRYPTION BY PASSWORD = 'Tomorrow never knows', DECRYPTION BY PASSWORD = 'All you need is love'); CREATE USER UnsafeSample_Login FROM CERTIFICATE UnsafeSample_Certificate; GRANT EXTERNAL ACCESS ASSEMBLY TO UnsafeSample_Login; But I'm getting error: "Cannot find the login 'UnsafeSample_Login4', because it does not exist or you do not have permission." Where i should change rights? User (which i'm using to connect to server) has "sysadmin" server role...
View Replies !
TRYING TO CREATE CERTIFICATE FOR SERVICE BROKER SECURITY BUT GETTING ERROR
hi all, i m trying to send message between different server instance using service broker. and for security purpose i am trying to create certificate. for that i have used makecert.exe and get a certificate and a private key. but when i am creating certificate using that file it is showing error the code is -- CREATE CERTIFICATE ctfSourceServerMaster FROM FILE = 'C:SourceServer.cer' WITH PRIVATE KEY ( FILE = 'C:SourceServer.pvk', DECRYPTION BY PASSWORD = 'PrivateKeyPassword' ) ACTIVE FOR BEGIN_DIALOG = ON GO i have created the file SourceServer.cer' and SourceServer.pvk' by using makecert.exe tool. the idea behind creating the certificate ctfSourceServerMaster is to give transport security. I am running the particular script in the master database. but still i am getting error ERROR:---- The certificate, asymmetric key, or private key file does not exist or has invalid format. If any body has any idea please help!!!!!!!!!!! Thanks a lot in advance
View Replies !
SQL2005 Data Import Error, Unicode Data Is Odd Byte Size For Column &&<n&&>. Should Be Even Byte Size
Hi, I have a problem importing data from SQL Server 2000 'text' columns to SQL Server 2005 nvarchar(max) columns. I get the following error when encountering a transfer of any column that matches the above. The error is copied below, Any help on this greatly appreciated... ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unicode data is odd byte size for column 3. Should be even byte size.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider) Many thanks
View Replies !
Certificate Loading Issue - When Creating Certificate From SQL Server To SQL Server Express On The Same Machine
Hi, We are trying to implement Service Broker between SQL Server Express and SQL Server on the Same machine and we are having problems with certificates. We are creating a certificate on SQL Server, backing up the certificate on a file system and then loading certificate on the SQL Server Express from the file and we are keep getting the following error: Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format. Following script runs fine on SQL Server. Code Snippet use master Create Master Key Encryption BY Password = '45Gme*3^&fwu'; BACKUP MASTER KEY TO FILE = 'C:ServiceBrokerPrivateKeyMasterB.pvk' ENCRYPTION BY PASSWORD = '45Gme*3^&fwu' Create Certificate EndPointCertificateC WITH Subject = 'C.Server.Local', START_DATE = '06/01/2006', EXPIRY_DATE = '01/01/2008' ACTIVE FOR BEGIN_DIALOG = ON; BACKUP CERTIFICATE EndPointCertificateC TO FILE = 'C:ServiceBrokerEndPointCertificateC.cer' Following script runs on SQL Server Express: Code Snippet Create Certificate EndPointCertificateC From FILE = 'C:ServiceBrokerEndPointCertificateC.cer' WITH PRIVATE KEY ( FILE = 'C:ServiceBrokerPrivateKeyMasterB.pvk', DECRYPTION BY PASSWORD = '45Gme*3^&fwu' ); If we run the script other way around, it works fine. If we use the SQL Server on some other machine, the script works fine. But only on the same machine, it throws this error. We made sure the permissions and everything. Let us know if there is any work around or what are we doing wrong. Any help is appreciated. Thank you,
View Replies !
Byte[] Value In SQL Query...
Hi people,I have a little problem with this!There are some variables in C# code: int personID = 10; string personName = "Tom"; System.IO.BinaryReader reader = new System.IO.BinaryReader(FileUploadPersonPhoto.PostedFile.InputStream); byte[] personPhoto = reader.ReadBytes(FileUploadPersonPhoto.PostedFile.ContentLength); After that, there is a SQL query: string query = "INSERT INTO PersonTable (PersonID, PersonName, PersonPhoto) VALUES (" + personID + ", '" + personName + "', " + personPhoto + ")"; In Debug mode, value of the query is "INSERT INTO PersonTable (PersonID, PersonName, PersonPhoto) VALUES (10, 'Tom', System.Byte[])" and it does not work! Is there any prefix or something else that I should put and make it work?Thank you in advance!P.S. Do not want to use sql parameters at this piont!
View Replies !
SQL Binary To C# Byte[]
I have a binary column with length 20 in SQL server table. I store dynamically C# byte[] value range from 0 to 19. So for example, If I store length of 16 and when I try to retrive it back into byte [] in C# it returns whole length 20. When I see in debug it has value from 0 to 15 which I want to use but from 16 to 19 is zero. How can I get just length value which I stored. I use DataRow to get whole row and from the row object I extract byte [] based on column name. Thank you in advance....
View Replies !
DATEADD With 8 Byte Int?
I have a bigint column called "MillisecondsSince1970" that I need to convert to a date - SSIS is erroring out when I use DATEADD with the 8 byte int (if I use 4 byte it works but the column is bigger than 4 byte). The error is really lame: [Derived Column [79]] Error: The "component "Derived Column" (79)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "Date" (100)" specifies failure on error. An error occurred on the specified object of the specified component. Anyone have a way around it... a VB.NET equivalent of DATEADD or something else I can do?
View Replies !
From Byte[] To A .rdl File
Hi, Can we regenerate a .rdl file based on the byte[] stream created by Render method? user sends the report name with parameters to application server and it is application server sends the request to Reporting Server and get the report back (Render method). But how to pass the returned byte[] stream to user and show her/him a report?
View Replies !
CLR UDT Max Byte Size
Hello, I need some help working with CLR UDTs. I have created two UDTs called trajectory and point. Each trajectory consists of a list of points. Each point consists of three members : lon( type double), lat( type double) and datetime. I have written my own IBinarySerialize.Write method for the trajectory type which is the following: Dim maxSize As Integer = 4000 Dim value As String = "" Dim paddedvalue As String Dim i As Integer Dim pt As Point For i = 0 To point_list.Count - 1 pt = point_list.Item(i) If i = 0 Then value = value & pt.X & "|" & pt.Y & "|" & pt.D Else value = value & ">" & pt.X & "|" & pt.Y & "|" & pt.D End If Next paddedvalue = value.PadRight(maxSize, ControlChars.NullChar) For i = 0 To paddedvalue.Length - 1 Step 1 w.Write(paddedvalue(i)) Next If I try to store 225 points for a trajectory then the following error occurs: System.Data.SqlTypes.SqlTypeException: The buffer is insufficient. Read or write operation failed. Why is that happening? The limit size for a UDT is 8000bytes. Each point needs 24Bytes (2*SizeOf(double) + sizeOf(Datetime)). Please help ST
View Replies !
Cannot Convet Sql Image To Byte[]
I have followed many examples found on this site, but still get an invalid cast execption when I attempt to run code below. the exception is thrown when I try to convert the sql image to a byte[] in the download section of the code. // // sqlUploadImage // this.sqlUploadImage.CommandText = "INSERT INTO Image_Table (Description, Type, Lenth, DocName) VALUES (@Description," + " @Type, @Length, @DocName)"; this.sqlUploadImage.Connection = this.sqlConnection2; this.sqlUploadImage.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Description", System.Data.SqlDbType.VarChar, 50, "Description")); this.sqlUploadImage.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Type", System.Data.SqlDbType.VarChar, 50, "Type")); this.sqlUploadImage.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Length", System.Data.SqlDbType.Int, 4, "Lenth")); this.sqlUploadImage.Parameters.Add(new System.Data.SqlClient.SqlParameter("@DocName", System.Data.SqlDbType.VarChar, 50, "DocName")); this.sqlUploadImage.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Image", System.Data.SqlDbType.Image, 16, "ImgField")); this.Button2.Click += new System.EventHandler(this.Button2_Click); // // sqlDownLoadImage // this.sqlDownLoadImage.CommandText = "SELECT Image_Table.* FROM Image_Table"; this.sqlDownLoadImage.Connection = this.sqlConnection2; //upload file private void Button1_Click(object sender, System.EventArgs e) { //Get the filename of the pdf file to be uploaded. string strFilename = File1.PostedFile.FileName.Substring(File1.PostedFile.FileName.LastIndexOf("\") +1); //Get the file type string strFileType = File1.PostedFile.ContentType; //Get the file size int intImageSize = File1.PostedFile.ContentLength; // Reads the Image Stream ImageStream = File1.PostedFile.InputStream; byte[] ImageContent = new byte[intImageSize + 1]; int intStatus = 0; intStatus = ImageStream.Read(ImageContent, 0, intImageSize); //Response.Write(strFilename.ToString()); //Response.Write("<br>Image Type: " + strFileType.ToString()); //Response.Write("<br>Image Size: "+intImageSize.ToString()); this.sqlUploadImage.Parameters["@Description"].Value = strFilename; this.sqlUploadImage.Parameters["@Type"].Value = strFileType; this.sqlUploadImage.Parameters["@Length"].Value = intImageSize; this.sqlUploadImage.Parameters["@DocName"].Value = "TestPDF"; this.sqlUploadImage.Parameters["@Image"].Value = ImageContent; try { this.sqlConnection2.Open(); this.sqlUploadImage.ExecuteNonQuery(); this.Label2.Text = "File Uploaded Successfully"; this.Button2.Visible = true; } catch(SqlException ex) { Response.Write(ex.ToString()); } finally { this.sqlConnection2.Close(); } } //download file private void Button2_Click(object sender, System.EventArgs e) { try { this.sqlConnection2.Open(); SqlDataReader r = this.sqlDownLoadImage.ExecuteReader(CommandBehavior.CloseConnection); if(r.Read()) { Response.ContentType = r["Type"].ToString(); byte[] image = (byte[])r["ImgField"]; Response.BinaryWrite(image); } } catch(SqlException ex) { Response.Write(ex.ToString()); } finally { this.sqlConnection2.Close(); } }
View Replies !
Doube Byte Characters
I want to store some double byte characters in a table. Originally I planed to change all data type of fields which will store double bytes from VARCHAR() to NVARCHAR(). But i just found out we have no problem to pull and store foreign content(double byte characters) in fields with VARCHAR type( the content is showing correct after stored in database) my question is : if VARCHAR can handle double byte character, what's the point of using NVARCHAR to store unicode character? any advice is appreciated. Michael ming.shi@factiva.com
View Replies !
Double-byte In MSSQL
Experts, i have trouble while insert/update a field which contains double-byte characters (Chinese Traditional). NO PROBLEM if i m using Enterprise Manager to view/edit the data. They are retrieved properly in the following: (1) Enterprise Manager (2) Query Analyzer (3) Visual Basic (4) Command prompt isql EACH of the Chinese words are become a qustion mark '?' if the UPDATE SQL or stored procedure executed in the following: (2) Query Analyzer (3) Visual Basic WHILE (4) Command prompt isql does not have the problem for the same UPDATE SQL and stored procedure. Do you have any idea?
View Replies !
Ftp Task- 0 Byte File
hi, i'm using FTP task in my control flow and using *.xml to receive all files at a particular ftp location. Often I see that a 0 byte file is created by the IS package, instead of transferring the file. Is this a known bug? Is there a way to circumvent it? Also, on one of the occasions, the package just hanged for about 60 minutes (while atttempting to receive the file). FTP Connection timeout was set to default (60 sec), I wonder why the FTP task didnt return a timeout error! Any clues? regards, Ali
View Replies !
Going From Byte() To File Open Of Download
Hello, I'm having users upload documents to my db and storing them as an Image datatype, I can do that without an issue. I'm also able to find that record and return it as a byte(). Now, what's the best practice/scheme to return it to the browser, even FF, and have it prompt the user with the Open or Download thing Dialogue we all know and love. Thanks in advance.
View Replies !
Storing Byte Array In Sql Server
Hello, I am using .net 1.1 and sql server 2000. I want to store a byte array, but it seems that only a string is stored there. Code: SHA512 sha = new SHA512Managed(); byte[] ReturnedPasswordByte = sha.ComputeHash(ToCryptByte); MyGenericCommand.Parameters.Add("@Password", ReturnedPasswordByte); MyConnection.Open(); MyGenericCommand.ExecuteNonQuery(); MyConnection.Close(); If I select the record later, all I get is "System.Byte[]" (as string)
View Replies !
Bulk Insert From Zero Byte File
Ok, hope someone has an idea out there. I have eight fixed width text files that I am importing into SQL 2000 tables in stored procedures called from a vb.net program. Problem is one of the files stands a good chance of being 0 k. As of I now i call one stored proc that then calls one proc for each file. If i try to run it anyhting after the zero byte file fails to import. If i remark it out everything else imports. I check for file.exists on the vb.net and the file passes the test. Any way to handle the 0 byte on the proc side
View Replies !
DTS Package - How To Prevent A 0 Byte File?
I've created a DTS package -- that uses a query to export to a .txt file. My question is -- if the results of this query are zero (no results returned within the package ) -- how can I tell the package not to export a zero byte file. Any thoughts on that? Any help you could give would be greatly appreciated. Thanks!
View Replies !
DTS: Handling Zero Byte Input Files
Good Day, I have a situation where I need to be able to gracefully handle zero byte text files to be processed by DTS (using ActiveX Script transformations). I have a job set up in SQL Server that executes several DTS packages, then executes a Transact SQL script to process the data imported by DTS. This all works fine, but there are instances where some of files have no data to be imported. This causes the entire job to fail -- I don't want to alter the job to continue on an error because there is a legimate potential for errors. I do, however, want to be to have the job continue if one of the DTS packages has no data to process. Is there a way (DTS object method, property) to check for the absence of data before the DTS package errors out? Any ideas would be greatly appreciated. Regards, Robin Sarac
View Replies !
Derived Column Can't Add 8-byte Integers
I'm having trouble adding a 4-byte integer with an 8-byte integer. Here's what I'm doing: Column Name: BIG_ID Derived Column: < add as new column > Expression: (DT_I8)[ID] + 840230000538058 Data Type: eight-byte signed integer {DT_I8] The error I get: The literal 840230000538058 is too large to fit into type DT_I4. The magnitude of the literal overflows the type. Then I try the expression: (DT_I8)[ID] + (DT_I8)840230000538058 and [ID] + 840230000538058 and get the same error. What am I doing wrong? Is it possible to add 2 8-byte integers in regular expression? Why does it still think the literal is DT_I4? Thanks, Michael
View Replies !
Help: 900 Byte Limit On Stored Procedure Parameter?
Hi,I have a .NET application that I want to save the Config.EXE contentsto my SQL database for remote review/testing. This config file is3700+ bytes long. I created a field in one of my tables with a VARCHAR4800 and then created a stored procedure that receives a parameter(also VARCHAR(4800).However it fails to write anything if the length of the value that Ipass is anything greater than 900. If I pass exactly 900 characters orless - the data is written to the field. If I pass 901 characters Iget nothing.I'm suspicious since it is exactly 900. I seriously doubt it's somelimitation of MS-SQL so I need a nudge in the right direction.Thanks
View Replies !
Breaking Data Into 1500 Byte Chunks
Hi, I have a text file (5 MB). It appears as a single line in a text editor. But actually it has records of 1500 byte length each. I want to strip it down to 1500 byte records. So 1500*3500 = 5 MB (approx). The record size is always 1500 bytes. Does anyone have a script that I can run on this file to achieve this break. Thanks
View Replies !
DTS - Zerp Byte File Import And DontExecuteTask
Hi, I'm a bit new to DTS but the problem I have encountered relates to importing a text file. On occasion the file is zero bytes which causes an error in DTS. I have added some VBScript to the workflow to check the filesize. If size > 0 then I set Main = DTSStepScriptResult_ExecuteTask otherwise Main = DTSStepScriptResult_DontExecuteTask. This all works fine except that when the file is 0 the dependent process that is waiting for completion/success does not run, presumably because the task was not run (as reqd). I have tried setting the status rather the DontExecuteTask but then the task runs anyway creating the error. Anyonw know how to get this to work or how to get around the problem? regards Ken
View Replies !
DTS Adding Byte To Columns During Table Migration
When migrating a table from Informix to SQL Server using DTS services, SQL Server adds an extra byte to the column lengths. Does anyone know why this happens and is there a way to prevent it? I'd like the source and target table to have the same column characteristics. Thanks.
View Replies !
Trying To Map To Tinyint - Single Byte Unsigned Int Not Working
I have an Excel spreadsheet that I eventually land into my staging table. In between, I'm attempting to get a date code from the Date table. I'm using a Lookup Transformation Editor and mapping the fiscal week of year and fiscal year name. I know the fiscal year name is fine. When I have both the fiscal year name and the fiscal week of year, the package fails on the lookup step. In a data conversion, I convert the fiscal week of year to a single byte unsigned integer. (In the Date table, the fiscal week of year is a tinyint.) I'm not sure what I'm doing wrong?
View Replies !
Connecting To Double Byte Progress (MFG-PRO) Databases From SSIS...
HelloWe use MFG-PRO as our ERP system which in turn uses Progressdatabases. In the old version of SQL 2000, using DTS packages, we usedto set the code page via command prompts and execute DTS packageswithin that command prompt to fetch data from our double byte ERPdatabases. In SSIS, we are able to connect and fecth data from NONDouble Byte databases, however, when we set the code page first andexecute via a command prompt the SSIS package, we get the followingerror. We used a ADO.NET connection to connect to our Source ERPSystem. Appreciate any help.ThanksJagannathan SanthanamMicrosoft (R) SQL Server Execute Package UtilityVersion 9.00.1399.06 for 32-bitCopyright (C) Microsoft Corp 1984-2005. All rights reserved.Started: 8:09:26 AMProgress: 2007-11-28 08:09:26.93Source: Data Flow Task 1Validating: 0% completeEnd ProgressError: 2007-11-28 08:09:27.15Code: 0xC0047062Source: Data Flow Task 1 DataReader Source [1]Description: System.Data.Odbc.OdbcException: ERROR [HYC00][DataDirect][ODBCPROGRESS driver]Optional feature not implemented.ERROR [HY000] [DataDirect][ODBC PROGRESS driver][PROGRESS]-219901ERROR [IM006] [DataDirect][ODBC PROGRESS driver]Driver'sSQLSetConnectAttr failed.ERROR [HYC00] [DataDirect][ODBC PROGRESS driver]Optional feature notimplemented..ERROR [HY000] [DataDirect][ODBC PROGRESS driver][PROGRESS]-219901ERROR [IM006] [DataDirect][ODBC PROGRESS driver]Driver'sSQLSetConnectAttr failed.at System.Data.Odbc.OdbcConnection.HandleError(OdbcHa ndle hrHandle,RetCode retcode)at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcCo nnectionconnection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)atSystem.Data.Odbc.OdbcConnectionFactory.CreateConne ction(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool,DbConnection owningObject)atSystem.Data.ProviderBase.DbConnectionFactory.Creat eNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)atSystem.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnectionowningConnection)atSystem.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnectionouterConnection, DbConnectionFactory connectionFactory)at System.Data.Odbc.OdbcConnection.Open()atMicrosoft.SqlServer.Dts.Runtime.ManagedHelper.GetM anagedConnection(StringassemblyQualifiedName, String connStr, Object transaction)atMicrosoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnec tionManager90.AcquireConnection(Object pTransaction)atMicrosoft.SqlServer.Dts.Pipeline.DataReaderSourceA dapter.AcquireConnections(Object transaction)atMicrosoft.SqlServer.Dts.Pipeline.ManagedComponentH ost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)End ErrorError: 2007-11-28 08:09:27.16Code: 0xC0047017Source: Data Flow Task 1 DTS.PipelineDescription: component "DataReader Source" (1) failed validationand returnederror code 0x80131937.End ErrorProgress: 2007-11-28 08:09:27.16Source: Data Flow Task 1Validating: 33% completeEnd ProgressError: 2007-11-28 08:09:27.16Code: 0xC004700CSource: Data Flow Task 1 DTS.PipelineDescription: One or more component failed validation.End ErrorError: 2007-11-28 08:09:27.16Code: 0xC0024107Source: Data Flow Task 1Description: There were errors during task validation.End ErrorDTExec: The package execution returned DTSER_FAILURE (1).Started: 8:09:26 AMFinished: 8:09:27 AMElapsed: 0.859 seconds
View Replies !
Connecting To Double Byte Progress (MFG-PRO) Databases From SSIS...
Hello We use MFG-PRO as our ERP system which in turn uses Progress databases. In the old version of SQL 2000, using DTS packages, we used to set the code page via command prompts and execute DTS packages within that command prompt to fetch data from our double byte ERP databases. In SSIS, we are able to connect and fecth data from NON Double Byte databases, however, when we set the code page first and execute via a command prompt the SSIS package, we get the following error. We used a ADO.NET connection to connect to our Source ERP System. What shocks me is that this functionality works well in SQL Server 2000 DTS packages but not in the much publicized SSIS packages. The error messages are so cryptic as they used to be in DTS days and do not help a wee bit! Appreciate any help. Thanks Jagannathan Santhanam Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:09:26 AM Progress: 2007-11-28 08:09:26.93 Source: Data Flow Task 1 Validating: 0% complete End Progress Error: 2007-11-28 08:09:27.15 Code: 0xC0047062 Source: Data Flow Task 1 DataReader Source [1] Description: System.Data.Odbc.OdbcException: ERROR [HYC00] [DataDirect][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [DataDirect][ODBC PROGRESS driver][PROGRESS]-219901 ERROR [IM006] [DataDirect][ODBC PROGRESS driver]Driver's SQLSetConnectAttr faile d. ERROR [HYC00] [DataDirect][ODBC PROGRESS driver]Optional feature not implemented . ERROR [HY000] [DataDirect][ODBC PROGRESS driver][PROGRESS]-219901 ERROR [IM006] [DataDirect][ODBC PROGRESS driver]Driver's SQLSetConnectAttr faile d. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode r etcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, Odb cConnectionString constr, OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOption s options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection own ingObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbC onnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection ow ningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection ou terConnection, DbConnectionFactory connectionFactory) at System.Data.Odbc.OdbcConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireCon nection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnection s(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnectio ns(IDTSManagedComponentWrapper90 wrapper, Object transaction) End Error Error: 2007-11-28 08:09:27.16 Code: 0xC0047017 Source: Data Flow Task 1 DTS.Pipeline Description: component "DataReader Source" (1) failed validation and returned error code 0x80131937. End Error Progress: 2007-11-28 08:09:27.16 Source: Data Flow Task 1 Validating: 33% complete End Progress Error: 2007-11-28 08:09:27.16 Code: 0xC004700C Source: Data Flow Task 1 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-11-28 08:09:27.16 Code: 0xC0024107 Source: Data Flow Task 1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:09:26 AM Finished: 8:09:27 AM Elapsed: 0.859 seconds
View Replies !
How To: Store And Retrieve Images In A Database As A Byte Array.
I€™ve inherited a project from one of the guys on our team who will be out sick for a while. He developed two for marshaling data between System.Drawing.Image and System.Byte(). He€™s storing the byte array data in a database image field. I€™ve retrieved the byte array data from his database image fields and have successfully converted them to images using his ConvertByteArrayToImage method below. I have also converted and image to a byte array with his ConvertImageToByteArray method below and succfully stored the data in a database image field. However, when I retrieve the byte array data that I stored in the database the last line in his ConvertByteArrayToImage method throws an exception (Parameter is not valid). I€™ve not been able to find a working copy of his code that€™s storing the byte array data. Does anyone see anything I€™m overlooking? Imports System.Drawing Imports System.IO Public Sub InsertImage(ByVal pFilename As String) Try Dim lImage As Image Dim lBA() As Byte Dim lSQL As String Dim lQuery As Alcon.SQLServer.Database.clsQuery Dim lParameters As New Alcon.SQLServer.Database.clsParameters lImage = Image.FromFile(pFilename) ConvertImageToByteArray(lImage, lBA) ' Initialization lQuery = New Alcon.SQLServer.Database.clsQuery(mConnection) lSQL = "" lSQL += "INSERT INTO [TBL_PCL_LENS_DATA](" lSQL += "[SerialNumber], " lSQL += "[ProcessedDate], " lSQL += "[CartonLabelImage]) " lSQL += "VALUES (" lSQL += "@SerialNumber, " lSQL += "@ProcessedDate, " lSQL += "@CartonLabelImage " lSQL += ")" lParameters.Add("@SerialNumber", SqlDbType.VarChar, ParameterDirection.Input, mSerialNumber) lParameters.Add("@ProcessedDate", SqlDbType.DateTime, ParameterDirection.Input, Now) lParameters.Add("@CartonLabelImage", SqlDbType.Image, ParameterDirection.Input, lBA) ' Execute query lQuery.Execute(lSQL, lParameters) Catch ex As Exception Throw End Try End Sub ConvertByteArrayToImage(ByVal pBA() As Byte, ByRef pImage As Image) Try ' Declaration Dim lMS As MemoryStream ' Initialization lMS = New MemoryStream(pBA, 0, pBA.Length) lMS.Write(pBA, 0, pBA.Length) lMS.Position = 0 ' Create image pImage = Image.FromStream(lMS, True) Catch ex As Exception Throw End Try End Sub ConvertImageToByteArray(ByVal pImage As Image, ByRef pBA() As Byte) Try ' Declaration Dim lBM As Bitmap Dim lBR As BinaryReader Dim lMS As New MemoryStream ' Initialization lBM = New Bitmap(pImage) lBM.Save(lMS, Imaging.ImageFormat.Png) lBR = New BinaryReader(lMS) ' Create byte array pBA = lBR.ReadBytes(lMS.Length) Catch ex As Exception Throw End Try End Sub
View Replies !
Searching A String With Asian Characters (double Byte)
My problem is that i can't search a field that contains Asian characters (Korean in this case). The table user_Access_tab have 2 keys: access_id nVarchar(50) user_id nVarchar(50) The sql query below is sent through a oledbcommand to a sql server 2005 database. "select access_id, access_right from user_Access_tab where user_id ='HQ001kimjo012007-05-07 ì˜¤ì „ 11:50:323401'" It doesn't show any hits even thogh i know there is a number of matching records. The question doesn't generate an answer in SQL server manager studio eigher. If i change the datatype on user_id to Varchar(50) the id is presented (in the database) as: HQ001kimjo012007-05-07 ?? 11:50:323401 Then the question works, but why doesn't it work with nVarChar(50)? Regards Martin Jonsson
View Replies !
Writing Byte Stream To Flat File Destination (ebcdic)
Hello all, I was trying to run a test to write a ebcdic file out with a comp - 3 number (testing this for other people) and have run into a problem writing the string out to the flat file destination. I have the following script component: Code Block ' Microsoft SQL Server Integration Services user script component ' This is your new script component in Microsoft Visual Basic .NET ' ScriptMain is the entrypoint class for script components Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Public Class ScriptMain Inherits UserComponent Public Overrides Sub CreateNewOutputRows() ' ' Add rows by calling AddRow method on member variable called "Buffer" ' E.g., MyOutputBuffer.AddRow() if your output was named "My Output" ' Output0Buffer.AddRow() Dim myByteArray() As Byte = {&H12, &H34, &H56, &H7F} Output0Buffer.myByteStream = myByteArray Output0Buffer.myString = "ABCD" Output0Buffer.myString2 = "B123" myByteArray = Nothing End Sub End Class I have added myByteStream as a DT_BYTES length 4, myString as (DT_STR, 4, 37) and myString2 as (DT_STR, 4, 37) to the output 0 buffer. I then add a flat file destination with code set 37 (ebcdic us / canda) with the corresponding columns using fixed width. When i place a dataviewer on the line between the two the output looks as I expect ("0x12 0x34 0x56 0x7F", "ABCD", "B123"). However, when it gets to the flat file destination it errors out with the following: Code Block [Flat File Destination [54]] Error: Data conversion failed. The data conversion for column "myByteStream" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". If i increase the size of the byte stream (say, to 50) the error goes away but I am left with the string "1234567F" instead of the appropriate hex values. Any clues on how to go about this? I obviously don't care if it gets transferred to "readable" text as this is supposed to be a binary stream, thus the no match in target page seems superfulous but is probably what is causing the problems. NOTE: this is relating to the following thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2300539&SiteID=1) in that I am trying to determine why these people are not seeing the "UseBinaryFormat" when importing an EBCDIC file (i see this fine when i use an ftp'd file, but it auto converts to ascii) with comp-3 values. I also see the "UseBinaryFormat" when I am importing a regular EBCDIC file which I create that has no import errors with zoned decimals.
View Replies !
Do Stored Procedures Have A Limit On Number Of Parameters Or Byte Size Passed In?
Hi,I'm using c# with a tableadapter to call stored procedures. I'm running into a problem where if I have over a certain byte size or number of parameters being passed into my stored proc I get an exception that reads: "Cannot evaluate expression because a thread is stopped at a point where garbage collection is impossible, possibly because the code is optimized." If I remove one parameter, the problem goes away. Has anyone run into this before? Thanks,Mark
View Replies !
Reg : Certificate
Hello all, can ayone tell me which exam i have to pass to get sql 2005 certificate andi n which site or link will get more information. would be appreciated . thanks folks.
View Replies !
Encryption With Certificate
I am trying to create a encrypted row in my database Everything here worked except that when i run the final query to decrypt the data It just comes up with null for each row. Even if i do a query to show me the rows that are not null It's like it is saying yeah there is data here but I am only going to show you null instead of what I am supposed to decrypt.Here is what I tried from start to finish Create Certificate TestCert Encryption By Password = 'Password' With Subject = 'SQLCert', Expiry_Date = '12/01/2050'; declare @Test nvarchar(50) set @Test='123456789' insert into testenc (testencry) Values (encryptbyCert(Cert_ID('TestCert'),@Test )) select convert (Nvarchar(50), DecryptByCert(Cert_ID('TestCert'), testencry,N'Password')) As Test from testenc
View Replies !
Certificate Not Found
Hello I haw trouble getting the service broker to work I have 3 instances of SQL servers: 1 €œSender€? SQL 2005 Server 2 €œReceiver 1€? SQLEXPRESS 2005 3 €œRecevier 2€? SQLEXPRESS 2005 What I wont is to be abele to do is to send a message from €œSender€? to €œReceiver 1€? or €œRecevier 2€?. I am abele to send a message from €œSender€? to €œReceiver 1€? but if I send a message to €œReceiver 2€? I get a dialog security problem I think. If I use profiler I can se in €œReceiver 2€? the events: Broker:Connection Audit Broker Login Broker:Message Classify Audit Broker Conversation = Certificate not found Broker:Message Undeliverable And I cant find what´s wrong, this Is my scripts for etch instance. €œSender€? USE master CREATE CERTIFICATE Cert_ROBOTSRV WITH SUBJECT = 'Cert_ROBOTSRV_auth', START_DATE = '02/15/2007', EXPIRY_DATE = '02/15/2015' GO BACKUP CERTIFICATE Cert_ROBOTSRV TO FILE = 'C:Cert_ROBOTSRV' GO CREATE ENDPOINT SBEndpointServer STATE = STARTED AS TCP (LISTENER_PORT = 5723) FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Cert_ROBOTSRV) GO CREATE USER andon CREATE CERTIFICATE Cert_sevapc311_pub AUTHORIZATION andon FROM FILE = 'C:Cert_sevapc311' --DROP CERTIFICATE Cert_Andonpc017_Trans CREATE CERTIFICATE Cert_Andonpc017_Trans AUTHORIZATION andon FROM FILE = 'C:Cert_andonpc017_Trans' --DROP LOGIN sbLogin CREATE LOGIN sbLogin FROM CERTIFICATE Cert_Andonpc017_Trans; GO GRANT CONNECT ON ENDPOINT::SBEndpointServer TO [public] GRANT CONNECT ON ENDPOINT::SBEndpointServer TO andon GO ---------------------------------------------------- USE AndonDB CREATE ROUTE Grafik_sevapc311 WITH SERVICE_NAME = 'Grafik_Service_Recive_sevapc311', BROKER_INSTANCE = '7C737F42-2DF6-46E7-A6B6-89D1A9608DE2', ADDRESS = 'TCP://sevapc311:5723' GO --DROP ROUTE Grafik_andonpc017 CREATE ROUTE Grafik_Andonpc017 WITH SERVICE_NAME = 'Grafik_Service_Recive_Andonpc017', BROKER_INSTANCE = 'AE2B294A-B02E-4709-A51E-CFBFD0E478C1', ADDRESS = 'TCP://192.168.20.106:5723' GO CREATE CERTIFICATE Cert_ROBOTSRV_Dialog WITH SUBJECT = 'Cert_ROBOTSRV_auth', START_DATE = '02/15/2007', EXPIRY_DATE = '02/15/2015' GO BACKUP CERTIFICATE Cert_ROBOTSRV_Dialog TO FILE = 'C:Cert_ROBOTSRV_Dialog' GO CREATE CERTIFICATE Cert_sevapc311_pub_Dialog AUTHORIZATION andon FROM FILE = 'C:Cert_sevapc311_Dialog' --DROP CERTIFICATE Cert_andonpc017_Dialog CREATE CERTIFICATE Cert_Andonpc017_Dialog AUTHORIZATION andon FROM FILE = 'C:Cert_andonpc017_Dialog' GRANT SEND ON SERVICE::[Grafik_Service_Send_ROBOTSRV] TO andon CREATE USER sbLogin GRANT SEND ON SERVICE::[Grafik_Service_Send_ROBOTSRV] TO sbLogin GO --GRANT SEND ON SERVICE::[Grafik_Service_Send_ROBOTSRV] --TO [VADERSTADvrobot] --GO -- Grant RECEIVE permission on the queue. GRANT RECEIVE ON [Grafik_Queue] TO andon GO GRANT CONTROL ON SERVICE::[Grafik_Service_Send_ROBOTSRV] TO andon GO --DROP REMOTE SERVICE BINDING Grafik_sevap CREATE REMOTE SERVICE BINDING Grafik_sevap TO SERVICE 'Grafik_Service_Recive_sevapc311' WITH USER = andon GO --DROP REMOTE SERVICE BINDING Grafik_andonpc017 CREATE REMOTE SERVICE BINDING Grafik_andonpc017 TO SERVICE 'Grafik_Service_Recive_Andonpc017' WITH USER = andon €œReceiver 1€? use master CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON' CREATE CERTIFICATE Cert_sevapc311 WITH SUBJECT = 'Cert_sevapc311_Auth', START_DATE = '02/15/2007', EXPIRY_DATE = '02/15/2015' GO BACKUP CERTIFICATE Cert_sevapc311 To FILE = 'C:Cert_sevapc311' CREATE ENDPOINT SBEndpointklient STATE = STARTED AS TCP (LISTENER_PORT = 5723) FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Cert_sevapc311) CREATE CERTIFICATE Cert_ROBOTSRV_pub AUTHORIZATION andon FROM FILE = 'C:Cert_ROBOTSRV'; GRANT CONNECT ON ENDPOINT::SBEndpointklient to andon ---------------------------------------------------------------------------------- use KlientDB GRANT SEND ON SERVICE::[Grafik_Service_Recive_sevapc311] TO andon GO GRANT CONTROL ON SERVICE::[Grafik_Service_Recive_sevapc311] TO andon GO GRANT RECEIVE ON [Grafik_Queue] TO andon GO CREATE Route Grafik_ROBOTSRV WITH SERVICE_NAME = 'Grafik_Service_Send_ROBOTSRV', BROKER_INSTANCE = '2BA192F8-0BA3-4237-A156-21AFF7C65481', ADDRESS = 'TCP://ROBOTSRV:5723' CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON' CREATE CERTIFICATE Cert_sevapc311_Dialog WITH SUBJECT = 'Cert_sevapc311_Auth', START_DATE = '02/15/2007', EXPIRY_DATE = '02/15/2015' GO BACKUP CERTIFICATE Cert_sevapc311_Dialog To FILE = 'C:Cert_sevapc311_Dialog' CREATE CERTIFICATE Cert_ROBOTSRV_pub_Dialog AUTHORIZATION andon FROM FILE = 'C:Cert_ROBOTSRV_Dialog'; €œReceiver 2€? use master CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON' --DROP CERTIFICATE Cert_Andonpc017_Trans CREATE CERTIFICATE Cert_Andonpc017_Trans WITH SUBJECT = 'Cert_Andonpc017_Auth', START_DATE = '02/15/2007', EXPIRY_DATE = '02/15/2015' GO BACKUP CERTIFICATE Cert_Andonpc017_Trans To FILE = 'F:Cert_Andonpc017_Trans' CREATE ENDPOINT SBEndpointklient STATE = STARTED AS TCP (LISTENER_PORT = 5723) FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Cert_Andonpc017_Trans) --DROP USER andon CREATE USER andon --DROP CERTIFICATE Cert_ROBOTSRV CREATE CERTIFICATE Cert_ROBOTSRV AUTHORIZATION andon FROM FILE = 'C:Cert_ROBOTSRV'; CREATE LOGIN sbLogin FROM CERTIFICATE Cert_ROBOTSRV; GO GRANT CONNECT ON ENDPOINT::SBEndpointklient TO [public] --Select * from sys.certificates --------------------------------------------------------------- use KlientDB create user andon Grant SEND ON SERVICE::[Grafik_Service_Recive_Andonpc017] to [Public] GRANT SEND ON SERVICE::[Grafik_Service_Recive_Andonpc017] TO andon GO GRANT CONTROL ON SERVICE::[Grafik_Service_Recive_Andonpc017] TO andon GO GRANT RECEIVE ON [Grafik_Queue] TO andon GO --DROP Route Grafik_ROBOTSRV CREATE Route Grafik_ROBOTSRV WITH SERVICE_NAME = 'Grafik_Service_Send_ROBOTSRV', BROKER_INSTANCE = '2BA192F8-0BA3-4237-A156-21AFF7C65481', ADDRESS = 'TCP://ROBOTSRV:5723' --Dialog Säkerhet CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON' --Drop CERTIFICATE Cert_Andonpc017_Dialog CREATE CERTIFICATE Cert_Andonpc017_Dialog WITH SUBJECT = 'Cert_Andonpc017_Auth', START_DATE = '02/15/2007', EXPIRY_DATE = '02/15/2015' GO BACKUP CERTIFICATE Cert_Andonpc017_Dialog To FILE = 'F:Cert_Andonpc017_Dialog' --Drop CERTIFICATE Cert_ROBOTSRV_Dialog CREATE CERTIFICATE Cert_ROBOTSRV_Dialog AUTHORIZATION andon FROM FILE = 'C:Cert_ROBOTSRV_Dialog';
View Replies !
Certificate Not Found
Hello, I have two different instances of sql server 2005 but i get Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89. This is one of the two instances: use master --ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003' create master key encryption by password = 'hello' create certificate [Certificato2] from file = 'c:certsTransportCert2.cer' with private key (FILE='c:certsTransportCert2.pvk', decryption by password='simone') active for begin_dialog = ON CREATE LOGIN [M02] WITH PASSWORD = 'wrPqYkr%bm3'; ALTER LOGIN [M02] DISABLE; CREATE USER [M02] FROM LOGIN [M02]; GO create certificate [Certificato1] authorization [M02] from file = 'c:certsTransportCert1.cer' active for begin_dialog = ON GO USE PublisherdDB --ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003' create master key encryption by password = 'hello' create certificate [CertificatoDialogo2] from file = 'c:certsDialogCert2.cer' with private key (FILE='c:certsDialogCert2.pvk', decryption by password='simone') active for begin_dialog = ON CREATE USER [Proxy::IsDbLookupRequestServiceM02] WITHOUT LOGIN; GO create certificate [CertificatoDialogo1] authorization [Proxy::IsDbLookupRequestServiceM02] from file = 'c:certsDialogCert1.cer' active for begin_dialog = ON CREATE REMOTE SERVICE BINDING [RSB::IsDbLookupRequestServiceM02] TO SERVICE 'IsDbLookupRequestServiceM02' WITH USER = [Proxy::IsDbLookupRequestServiceM02], ANONYMOUS = OFF; GO CREATE ROUTE [Route::IsDbLookupRequestServiceM02,D516E70B-59D6-4BF4-882A-BDA7ACD6EB07] WITH SERVICE_NAME = 'IsDbLookupRequestServiceM02', ADDRESS = 'tcp://PORTATILEXP:4022'; GO GRANT SEND ON SERVICE::[IsDbLookupResponseService] TO [Proxy::IsDbLookupRequestServiceM02] GO USE MASTER CREATE ENDPOINT [BROKER] AUTHORIZATION [VIDEOSYSTEMSimone_Farinea] STATE=STARTED AS TCP (LISTENER_PORT = 4033, LISTENER_IP = ALL) FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED , MESSAGE_FORWARD_SIZE = 10 , AUTHENTICATION = CERTIFICATE [Certificato2] , ENCRYPTION = REQUIRED ALGORITHM RC4) GRANT CONNECT ON ENDPOINT::[BROKER] TO [M02]; Here is the second one: use master --ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'hello' create master key encryption by password = 'hello' create certificate [Certificato1] from file = 'c:certsTransportCert1.cer' with private key (FILE='c:certsTransportCert1.pvk', decryption by password='simone') active for begin_dialog = ON CREATE LOGIN [SIMONEX] WITH PASSWORD = 'wrPqYkr%bm3'; ALTER LOGIN [SIMONEX] DISABLE; CREATE USER [SIMONEX] FROM LOGIN [SIMONEX]; GO create certificate [Certificato2] authorization [SIMONEX] from file = 'c:certsTransportCert2.cer' active for begin_dialog = ON GO USE vsi --ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'hello' create master key encryption by password = 'hello' create certificate [CertificatoDialogo1] from file = 'c:certsDialogCert1.cer' with private key (FILE='c:certsDialogCert1.pvk', decryption by password='simone') active for begin_dialog = ON CREATE USER [Proxy::IsDbLookupResponseService] WITHOUT LOGIN; GO create certificate [CertificatoDialogo2] authorization [Proxy::IsDbLookupResponseService] from file = 'c:certsDialogCert2.cer' active for begin_dialog = ON GRANT SEND ON SERVICE::[IsDbLookupRequestServiceM02] TO [Proxy::IsDbLookupResponseService] GO CREATE ROUTE [Route::IsDbLookupResponseService,88EB00C4-8CA9-4B45-9899-677AA70818B1] WITH SERVICE_NAME = 'IsDbLookupResponseService', ADDRESS = 'tcp://SIMONEX:4033'; GO USE MASTER CREATE ENDPOINT [BROKER] AUTHORIZATION [VIDEOSYSTEMSimone_Farinea] STATE=STARTED AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL) FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED , MESSAGE_FORWARD_SIZE = 10 , AUTHENTICATION = CERTIFICATE [Certificato1] , ENCRYPTION = REQUIRED ALGORITHM RC4) GRANT CONNECT ON ENDPOINT::[BROKER] TO [SIMONEX]; What's wrong in my code? Many thanks.
View Replies !
Removing A Certificate
Hi i'm having issues removing this certificate select * from sys.certificates I see cert_xp_cmdshell_enabler 257 1 NA NO_PRIVATE_KEY 1 Enable xp_cmdshell c5 30 7d 58 ba 8e 96 a6 48 48 df ca 87 bc 91 44 0x010600000000000901000000BEC4CB23E7994CFF1E71403F906BB1EFFDDEAF9A S-1-9-1-600556734-4283210215-1061187870-4021382032-2595217149 Enable xp_cmdshell 2009-04-28 10:31:31.000 2007-04-28 10:31:31.000 0xBEC4CB23E7994CFF1E71403F906BB1EFFDDEAF9A NULL DROP CERTIFICATE cert_xp_cmdshell_enabler he certificate cannot be dropped because one or more entities are either signed or encrypted using it. Is there any way I can drop this as I would like to recreated it and add a whole host of sps that I need to use the xp_cmdshell. Thanks in advance.
View Replies !
Certificate Encryption
i'm totally stuck, i know i must be missing something, just can't find it.. here's my situation: tbl_user (ssn char(9), ssn_encrypted varchar(9)); CREATE CERTIFICATE AUTOCERT WITH SUBJECT = 'Salad', EXPIRY_DATE = '1/1/2099';GO UPDATE tbl_userset ssn_encrypted = encryptbycert(cert_id('AUTOCERT'),SSN)go select ssn, ssn_encrypted from tbl_user results look good. encryption worked. SELECT ssn, decryptbycert(cert_id('AUTOCERT'),ssn_encrypted) FROM tbl_user results of select statement show ssn_encrypted = null for every record. why is it null? how can i get decryption to work?
View Replies !
3 Questions About Certificate
As we know ,certificate is a digitallly-signed security object that bind the public key to the principal who holds the private key. Say i create a certificate by using create certificate DDL, then use it to encrypt data and decrypt data as follows: Code Block create certificate cert1 encryption by password='p@ssw0rd1' with subject='certificate test' go declare @plaintext varchar(100) set @plaintext='abcd' declare @cipher varbinary(8000) set @cipher=encryptbycert(cert_id('cert1'),@plaintext) select convert(varchar,decryptbycert(cert_id('cert1'),@cipher,N'p@ssw0rd1')) Is that mean i create public key implicitly when i run "create certificate cert1 encryption by password='p@ssw0rd1' with subject='certificate test'"? What cryptography algorithm would be use when i use the certificate to encrypt data ? Where is private key? I ensure I use the public key which generated from "create certificate " DDL to encrypt data .I want to know which private key i used when i execute decryptbycert to decrypt.
View Replies !
Cannot Drop Certificate.
im trying to drop all certificates on my database, and then the master key, but cannot do this as there are objects encrypted by one particular cert that i called fcert. I done this ages ago and cannot remember what i encrypted with this cert. to drop the master key i have to drop the cert and to drop the cert i have to make sure no objects are encrypted by it. how is this achieved?
View Replies !
Public Key In Certificate
<code> CREATE CERTIFICATE SalesCert encryption by password = 'blabla' WITH SUBJECT = 'Sales cert', EXPIRY_DATE = '10/31/2009' GO </code> When I create certificate using such tsql where is the public key.
View Replies !
|
TRACKER
