Good day to ALL,
I have already setup a db to my client.
My sp's are all created using CLR.
If my sp's are changed, how do I deploy them back to my client?
I usually send them a backup of the DB during the first few implementation.
But currently, their DB now contains live data, so I can't just let them restore the backup.
Is there another way?
Thanks and more power!
Arthur
Hi
I'm developing a tool to deploy the CLR objects from any where to the server
But I'm unable to deploy from remote system to the server.
I'm getting the error like
CREATE ASSEMBLY failed because it could not open the physical file 'D:WorkingDBS2005V2GLGLREPORTSDBS.GL.RM.SQLinDebugsample.dll': 3(The system cannot find the path specified.).
when I try to deploy using
EXEC dbo.sp_executesql @statement = N' CREATE ASSEMBLY [DBS.GL.RM.SQL]
FROM ''D:WorkingDBS2005V2GLGLREPORTSDBS.GL.RM.SQLinDebugsample.dll''
WITH PERMISSION_SET = SAFE'
can anybody tell how to generate assembly_bits for assembly
the VS.NEt is generating assembly bits instead of physical path to deploy the assembly on the remote server.
as
CREATE ASSEMBLY [sample]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD215 ..
WITH PERMISSION_SET = SAFE
But nothing is provided reg this in MSDN.
will u help me in handle this please
Thank u
Hi,
when deploying assembly from VS into SQL Server 2005, deployment fails with these error messages:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I'm deploying assembly with UNSAFE permission setting.
What's wrong, please?
Thanx, Fipil
Hello,
I think I have some kind of permission problem. But first things first:
I have code which I would like to run in SQL Server (CLR Integration). First thing is that my code uses third-party-dll. I have to deploy my code as unsafe because of
"
CREATE ASSEMBLY failed because method "add_FunctionAdd" on type "USP.Express.Pro.FunctionsCollection" in safe assembly "USP.Express.Pro.2.0" has a synchronized attribute. Explicit synchronization is not allowed in safe assemblies.
"
Of course I can not create "asymmetric key" for third-party-dll (Or can I?).
So, I tried to use trustworthy DB. But I get all the time error Msg 10327: "Assembly is not authorised for PERMISSION_SET=UNSAFE"
I am using Windows Login to log on Sql Server. Login is granted "Unsafe assembly" and DB has trustworthy setting "on".
Login has server roles "sysadmin" and "securityadmin".
Login is mapped with DB User who has same name ( DOMAINUserName ) and has default schema "dbo".
Login has DB memberships "db_owner" and "db_securityadmin".
DB user owns schemas "db_owner" and "db_securityadmin".
Am I missing something?
Interesting thing is that I can do deployment (as unsafe assembly) in master-database. But not in the other databases.
Questions are:
- Is there other way to authorise third-party-dll than using trustworthy?
- Why deployment can be done in master-database?
And finally:
- Why deployment can not be done in other database?
We have a custom assembly that worked fine with SSRS 2000. Now that we have upgraded to SQL 2005 we get an "Access is denied" error when attempting to deploy reports that reference this assembly. I could swear that when we first attempted it it worked. We decided to make a minor change to the assembly code and that's when the problem started, even though I've since reverted back to the original code to make certain that the minor change wasn't the issue. The assembly is in the proper folder - C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportServerin - and the reports are referencing the current version. I've searched high and low on the web for an answer but have found nothing even remotely helpful. All this code does is return a string...it's not doing anything that should require any additional security; and even then I doubt if there would be a problem when deploying.
The full text of the error is:
Error while loading code module: €˜Reporting, Version=1.0.2778.12272, Culture=neutral, PublicKeyToken=null€™. Details: Could not load file or assembly 'Reporting, Version=1.0.2778.12272, Culture=neutral, PublicKeyToken=null' or one of its dependencies. Access is denied.
Any ideas? Anyone? I'm at my wit's end on this!
Thanks,
Mike
I have created reports which access LDAP to get Employee Information.
If i remove LDAP code & only take fields from database report works.
If i use LDAP code which is in .NET assembly, It gives #error instead of showing employee name.
It works in development enviorment. but when i deploy it to reporting server it gives above error.
Development & server are in same network.
I am trying with following link also but no luck
http://blogs.sqlxml.org/bryantlikes/pages/824.aspx
Regards
I work with February CTP of SqlServer 2008.
I have an Assembly with several UDTs inside. Version of assembly is 1.0.*
I use CREATE ASSEMBLY statement to register this assembly, and it runs without any errors. Then I rebuild CLR solution without doing any changes in source code. In that case the only difference between new and old assemblies is version (difference in fourth part of version).
Then I try to update assembly in SqlServer. I use
ALTER ASSEMBLY <name>
FROM <path>
WITH PERMISSION_SET = UNSAFE, UNCHECKED DATA
statement for this. Statement runs with error:
Msg 6509An error occurred while gathering metadata from assembly €˜<Assembly name>€™ with HRESULT 0x1.
I found the list of condition for ALTER ASSEMBLY in MSDN:
ALTER ASSEMBLY statement cannot be used to change the following:
· The signatures of CLR functions, aggregate functions, stored procedures, and triggers in an instance of SQL Server that reference the assembly. ALTER ASSEMBLY fails when SQL Server cannot rebind .NET Framework database objects in SQL Server with the new version of the assembly.
· The signatures of methods in the assembly that are called from other assemblies.
· The list of assemblies that depend on the assembly, as referenced in the DependentList property of the assembly.
· The indexability of a method, unless there are no indexes or persisted computed columns depending on that method, either directly or indirectly.
· The FillRow method name attribute for CLR table-valued functions.
· The Accumulate and Terminate method signature for user-defined aggregates.
· System assemblies.
· Assembly ownership. Use ALTER AUTHORIZATION (Transact-SQL) instead.
Additionally, for assemblies that implement user-defined types, ALTER ASSEMBLY can be used for making only the following changes:
· Modifying public methods of the user-defined type class, as long as signatures or attributes are not changed.
· Adding new public methods.
· Modifying private methods in any way.
But I haven€™t done any changes in source code, so new version of assembly satisfies all this conditions.
What could be the reason for such behavior?
P.S. I€™ve got the same error, if I add or change any method in assembly before rebuilding.
I am trying to get a function I created in VB 5 for Access and Excel to work in SQL 2005. I was able to update the old VB code to work in VB 2005. I compiled and made a .dll, and I was able to register the new Assembly in SQL Server. When I try to create the Function, I get an error:
CREATE FUNCTION dbo.Temperature(@FluidName char, @InpCode Char, @Units Char, @Prop1 varchar, @Prop2 varChar)
RETURNS VarChar
AS EXTERNAL NAME FluidProps.[FluidProps.FluidProperties.Fluids].Temperature
Error returned:
Msg 6573, Level 16, State 1, Procedure Temperature, Line 21
Method, property or field 'Temperature' of class 'FluidProps.FluidProperties.Fluids' in assembly 'FluidProps' is not static.
Here is the code (part of it) in the VB class:
Header:
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports System.Runtime.InteropServices
Imports System.Security
Imports System.Security.Permissions
Namespace FluidProperties
'Option Strict Off
'Option Explicit On
Public Partial Class Fluids
Function:
Function Temperature(ByRef FluidName As Object, ByRef InpCode As Object, ByRef Units As Object, ByRef Prop1 As Object, Optional ByRef Prop2 As Object = Nothing) As Object
Call CalcProp(FluidName, InpCode, Units, Prop1, Prop2)
Temperature = ConvertUnits("-T", Units, T, 0)
End Function
If I change the Function Temperature to Static, I get an error that functions cannot be Static. Its been a long time since I created the code and am having a hard time in my older age of getting the cobwebs out to make it work.
I have no problem getting the function to work if I call it from a VB form....so what do I need to do to get it to work on data in my SQL server?
Thanks
Buck
I previously had an ASP.NET 1.1 site running on my IIS 6.0 server (not the default website) with Reporting Services running in a subdirectory of that website. I recently upgraded to ASP.NET 2.0 for my website and was greeted with an error when trying to view a report. The error was very non-descript, but when I checked the server logs, it recorded the details as "It is not possible to run two different versions of ASP.NET in the same IIS process. Please use the IIS Administration Tool to reconfigure your server to run the application in a separate process."
First of all, I could not figure out where and how to do this. Secondly, I decided to try to also change the Reporting Services folders to run ASP.NET 2.0 and when I did, I was greeted with the following message when attempting to view a report:
"Failed to load expression host assembly. Details: StrongName cannot have an empty string for the assembly name."
Please help.
Hi
I am a bit paranoid about what I just did to my SQL Server 2005 with this CLR experiment.
I created a Class Lib in C# called inLineLib that has a class Queue which represents an object with an ID field.
in another separate namespace called inLineCLRsql, I created a class called test which will hold the function to be accessed from DB, I referenced and created an instances of the Queue class, and retrieve it's ID in a function called PrintMessage.
namespace inlineCLRsql{
public static class test{
public static void PrintMessage(){
inLineLib.Queue q = new inLineLib.Queue();
int i = q.queueId ;
Microsoft.SqlServer.Server.SqlContext.Pipe.Send(i.ToString());
}
}
}
to access this from the db, I attempted to create an assembley referencing inLineCLRsql.dll. This didn't work as it complained about inLineLib assembly not existing in the db. I then attempted to create an assembley for inLineLib but it barfed saying System.Management assembly not created.
so what I did is (and this is where I need to know if I just ruined sql server or not):
1- ALTER DATABASE myDB SET TRUSTWORTHY ON;.
2- CREATE ASSEMBLY SystemManagement
FROM 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727System.Management.dll'
WITH PERMISSION_SET = UNSAFE
3- CREATE ASSEMBLY inLineLibMaster
FROM 'D:inLineServerinLineLibinDebuginLineLib.dll'
WITH PERMISSION_SET = unsafe
4- and finally
CREATE ASSEMBLY inLineLib
FROM 'D:inLineServerCLRSQLinlineCLRsqlinDebuginlineCLRsql.dll'
WITH PERMISSION_SET = SAFE
Everything works after those steps (which took some trial and error). I can create a sproc like:
CREATE PROC sp_test AS
EXTERNAL NAME inLineLib.[inlineCLRsql.test].PrintMessage
and it returns the Queue ID
Is there anything unadvisable about the steps above?
Thanks for your help
M
Am getting errors trying to deploy a dtsx created by ms (the reporting services execution log one) to which I have made zero changes, but it is not working (2 errors shown below)
error from deployment wizard:
===================================
Could not save the package "H:SSISRSlogRSExecutionLog_UpdateinDeploymentRSExecutionLog_Update.dtsx" to SQL Server "xxxxxxxxxxx". (Package Installation Wizard)
===================================
The SaveToSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed.
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServer(Package package, IDTSEvents events, String serverName, String serverUserName, String serverPassword)
at Microsoft.SqlServer.Dts.Deployment.DtsInstaller.SavePackageToSqlServer(WizardInputs wizardInputs, String packagePassword, Boolean bUseSeverEncryption, String serverName, String userName, String password, String packageFilePath, List`1 configFileNames)
at Microsoft.SqlServer.Dts.Deployment.DtsInstaller.InstallPackagesToSqlServer(WizardInputs wizardInputs)
===================================
The SaveToSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed.
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.SaveToSQLServer(IDTSPackage90 Package, IDTSEvents90 pEvents, String ServerName, String ServerUserName, String ServerPassword)
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServer(Package package, IDTSEvents events, String serverName, String serverUserName, String serverPassword)
error from sql management studio
===================================
Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)
We have written a test CRL stored procedure to test replacing one of our complex stored procedures but can€™t get it deployed to our SQL server that hosts a mirrored configuration of our production database (very locked down). It works fine on our development instances (not very locked down). It only references the default assemblies that were added when we created the project. All it does is use Context Connection=true to get data, loops though some records and returns the data using SQLContext. CLR is enabled on SQL server, the assembly is strongly signed, and we tried deploy using the binary string with the SAFE setting.
CREATE ASSEMBLY for assembly 'SQLCLRTest2' failed because assembly 'SQLCLRTest2' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : SQLCLRTest2.StoredProcedures::GetLift][mdToken=0x600001e] Type load failed.
[token 0x02000008] Type load failed.
I am trying to deploy a Database Project with Visual Studio 2005 and SQL Server 2005 Standard.
I import €œSystem.IO€? and have therefore set the permission levels to EXTERNAL_ACCESS.
I am receiving the same error message that many folks have received.
CREATE ASSEMBLY for assembly 'Images' failed because assembly 'Images' 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. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem. Images.
My CLR access is €œon€?
I have tried
1) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to [BuiltinAdministrators].
2) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to €œMy Windows Authentication ID€?.
3) Run ALTER DATABASE MYDATABASE SET TRUSTWORTHY ON
4) In Visual Studio .NET 2005 Set the permission levels to €˜external€™
5) Tried BuiltinAdministrators and my SQL Server Windows Authenticated Login ID for the ASSEMBLY OWNER.
I can compile BUT NOT DEPLOY
Any help would be greatly appreciated.
Regards Steve
I was trying to understand how VS.NET2005 was deploying .NET CLR assemblies to SQL2005 so I ran a trace and found some interesting results.
VS.NET creates some SQL that looks pretty interesting:
CREATE ASSEMBLY [AssemblyNameHere]
FROM 0x4D5A90000300000004000000FFFF000......<continue binary data>
WITH PERMISSION_SET = EXTERNAL_ACCESS
Boy howdy!
I have tried to reproduce this and create my own deployment application but I cant figure out how they create this binary stream. The info in BOL is not much help and I have not found any samples anywhere on how to create this stream in c#.
Anyone out there been able to get this to work?
-Ben
Hi there.
I have an assembly, call it A1, that I've deployed to a SQL Server 2005 database. I can use the managed stored procedures from A1 in SQL Server no problem.
In A1 there is a bit of code which uses the Assembly.Load() method, so load another assembly and use instances of class found in that external assembly. However, when I run the managed stored proc in A1 that uses Assembly.Load() I get the error:
Could not load file or assembly 'A1, Version=1.0.0.0, Culture=neutral,PublicKeyToken='????' or one of its dependencies. The system cannot find the file specified.
(note: for security I've changed some of the above line).
So I changed the Assembly.Load() to use
System.Data,Version=2.0.0.0,Culture=neutral,PublicKeyToken=b77a5c561934e089
I re-built the project, re-deployed it and ran the code in SQL Server - it worked. I could create an instance of a System.Data.DataSet for example. So why can't I load my own custom assembly? My assembly does have a strong name and it's installed in the GAC. I wrote a console app to try and Assembly.Load() my custom assembly and that worked fine (it was also running on the same server as the SQL Server).
So it's defiantely the SQL Server that can't 'see' my customer assembly. What do I need to do this assembly so that SQL Server will allow me to Assembly.Load it, just as it can with System.Data?
Thanks
Jas.
Hi,
I have one ClassLibrary three member functions.I have the dll for this...Can some one please tell me how to create a Assembly in .NET.
I need to create a simple private assembly with the dll that i have.
Thanks
I want use Microsoft.Office.Interop.Excel to access excel file my clr. But that assembly is installed in GAC. How could I register it in SQL 2005?
thanks!
I'm using SQL Server Management Studio Express and am unable to create an assembly. The following code:
create assembly HelloWorld from 'c:HelloWorld.dll'
with permission_set = safe
returns the following error:
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'assembly'.
I noticed that the word "assembly" isn't in blue text and what's more, if I expand the programmability node on any database, there is no assembly node. Anybody know what I'm doing wrong here?
Thanks in advance.
Is there a way to retrieve the methods within an assembly that are attributed with SqlTrigger or SqlProcedure using T-SQL?
View 1 Replies View RelatedI'm trying to add an assembly to my SSDT project, and in my assembly, it requires EXTERNAL access, which also means that the assembly has to be signed, and an asymmetric key must be created from the assembly. Â I can't figure out how to get this to work from the SSDT project.
View 8 Replies View Related Hi.
One of my databases has an assembly that uses system.web. Therefore, in the deploy script, the developer has put in a create assembly for it like so
Create Assembly [System.Web]
FROM 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727System.Web.dll'
WITH PERMISSION_SET = UNSAFE
followed by the create assembly statements for the actual bespoke assemblies.
The deploy script all works fine, as do the assemblies it creates.
However, when SQL restarts, the calls to the assemblies no longer work, and the only way to get them to is to run the deploy script (which drops them if they exist and recreates them) again.
Not particularly familiar with 2005 & CLR. Is this normal behaviour (I would have expected that once deployed, they were there after re-boot). If it's not how things are supposed to work, what's wrong, and how do I fix it?
Thanks
Dears,I have devoloped an application ASP.NET 2.0.Before I have builded the aspnetdb throught the command then I built some tables and stored procedures on this db. (My db is sql express 2005 and my hoster db is sql 2005 workgroup)My hoster doesn't allow connection Management studio express, doesn't allow attach or restore functionalities.Than I have built my script db (contains Tables, Views and Stored Procedures), I have substituted the dbo with my user account in the script because my hoster doesn't allow the dbo access.I have also transfered my site files (.aspx, img, etc.) on the server.When I try to access the db I receive this error (for example when I push on the button create user):The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file. Please could you help me? Thank you.
View 1 Replies View RelatedHi All,
Can anyone tell me,is it possible to create an exe or msi for sql stored procedures,tables and triggers?I want to deploy the database objects(stored proc,tables,views and functions) as an exe file..just like publishing and deploying the asp.net application.Is it possible for sql server database objects.Pls,let me know.
Thank U
I'm deploying an ASP .Net application on which 20 users will be working at the same time. The database will start at less than 100mb. Is SQL EXPRESS 2005 powerfull enough to use for such an application. Thanks in advance for your answers
View 1 Replies View RelatedI have a report that I am trying to deploy. I right-clicked my report in the solutions explorer, then select "deploy" but then I get the following error:
Microsoft Report Designer
The project cannot be deployed because no target server is specified. Provide a value for the TargetServerURL property in the property page for this project.
So then I go to the properties page but there isn't a TargetServerURL field! There is only a file name and full path field. Does anyone know what is wrong? Could it be that I need IIS installed on my machine?
More info:
Software installed from http://www.microsoft.com/express/sql/download/default.aspx:
-Microsoft SQL Server 2005 Express Edition
-SQL Server Management Studio Express
-Install Microsoft SQL Server 2005 Express Edition with Advanced Services
-Microsoft SQL Server 2005 Express Edition Toolkit
Windows XP Home Edition
Hi, all,
I am looking for a way to deploy SSIS pkg from dev to test/prod.
Both BOL and MSDN/TecNet samples are complicated. Do I miss the old good days of DTS!
Thanks!
Hi all,I'm new to SQLServer (Express edition) so I was wondering: if the webhost supports SQLServer 2005 do I just need to move the .MDF file to mydirectory on the web host to be able to use it?Thanks,Lorenzo
View 3 Replies View RelatedHi all,
I have created a database in MS-Access. I want to deploy that database on the local server, how can i do it?
Let me clear, i have created a DSN for my database. I want to deploy a report on RServer(on SQL Server2005). This DSN will be the datasource for the report. When i am trying to deploy this report with this DSN i am getting an error message
"Cannot create a connection to data source 'DataSource2'. (rsErrorOpeningConnection)". Could any one help me?
Thanks,
Hi, there;
I created a report using Report Sevice, but I cannot deploy it because of the security issue. If I try to open IIS, I got "Access Denied" message.
I checked another two developers' user settings, they are in the same user group as I am, but they can create/deploy report service.
So what should I need to create/deploy my report service?
cheers.
I have created a report project file on my local machine.Now I'm trying to deploy those reports to a development server. I have installed sql server and tools in my local machine but never configured reporting services. When I try to deploy those reports, it asks username and password for reporting services login.
But usually as far as I know, i should be able to deploy my reports without entering password etc. Even if I enter username and password, still they dont work.
Is that becase I install SQL server in my local machine?
Does anyone know why?
Thanks
I am trying to deploy a project to localhost, but I get the following error. I have only one package in this project.
"Could not save the package "C:......lah.dtsx" to SQL Server "localhost".
I tried deploying other projects that I've successfully deployed before and those also fail with the same message. Am I missing something really basic here?
Hi,
I have a web project and created a setup project for it. And I have to create reports using sql server reporting services. For this I have created an reportserver project and created two reports.
I have to create a setup file to install these reports in a remote machine.
How to do this?
where this .rdl and .rds files will be stored in the reportserver.
Regards,
Murali
I'm attemping to deploy a crypto class that we use on a desktop app to SQL Server. I'll use the encryptstring/decryptstring found in this class as User Defined Functions on SQL Server. When I try to deploy the class I'm getting this error:
Error 1 Method, property or field 'EncryptString' of class 'EncryptingDecryptingOASISPWDS.Crypto' in assembly 'EncryptingDecryptingOASISPWDS' is not static. EncryptingDecryptingOASISPWDS
Here is the code:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Security.Cryptography
Imports System.Text 'for UnicodeEncoding
Imports System.IO 'for MemoryStream, FileStream, Stream, Path
Imports System.Threading 'for Thread.Sleep
Partial Public Class Crypto
'Private Const CodeVersion As String = "01.00.00"
'class member variables
Private m_sPassPhrase As String
'class private variables
Private mbytKey() As Byte 'crypto key
Private mbytIV() As Byte 'Initialization Vector
Private mbKeyIsSet As Boolean = False
Private mksKeyNotSetException As String = "Crypto passphrase is not set."
Private mksPassphraseTooSmall As String = "PassPhrase length must be at least {0} characters."
'--- class constructors
Public Sub New()
'no passphrase is useful for GetHashString/ValidPassword methods
End Sub
Public Sub New(ByVal CryptoPassPhrase As String)
PassPhrase = CryptoPassPhrase
End Sub
'--- class public properties
'generate and store encryption key & iv from passphrase
Public Property PassPhrase() As String
Get
Return m_sPassPhrase
End Get
Set(ByVal Value As String)
Const iMinLength As Integer = -1 '-1 disables min length
m_sPassPhrase = Value.Trim
'enforce a rule on minimum length if desired here
If (Value.Length > iMinLength) Or (iMinLength = -1) Then
Dim sha2 As New SHA256Managed()
'256 bits = 32 byte key
mbytKey = sha2.ComputeHash(BytesFromString(m_sPassPhrase))
'convert to Base64 for Initialization Vector, take last 16 chars
Dim sKey As String = Convert.ToBase64String(mbytKey)
mbytIV = Encoding.ASCII.GetBytes(sKey.Remove(0, sKey.Length - 16))
mbKeyIsSet = True
sha2 = Nothing
Else
mbKeyIsSet = False
Throw New Exception(String.Format(mksPassphraseTooSmall, (iMinLength + 1).ToString))
End If
End Set
End Property
'decrypt a stream
Public Function DecryptStream(ByVal EncryptedStream As MemoryStream) As MemoryStream
If mbKeyIsSet Then
Try
'create Crypto Service Provider, set key, transform and crypto stream
Dim oCSP As New RijndaelManaged()
oCSP.Key = mbytKey
oCSP.IV = mbytIV
Dim ct As ICryptoTransform = oCSP.CreateDecryptor()
Dim cs As CryptoStream = New CryptoStream(EncryptedStream, ct, CryptoStreamMode.Read)
'get bytes from encrypted stream
Dim byteArray(EncryptedStream.Length - 1) As Byte
Dim iBytesIn As Integer = cs.Read(byteArray, 0, EncryptedStream.Length)
cs.Close()
'create and write the decrypted output stream
Dim plainStream As New MemoryStream()
plainStream.Write(byteArray, 0, iBytesIn)
Return plainStream
Catch ex As Exception
Return Stream.Null
End Try
Else
Throw New Exception(mksKeyNotSetException)
End If
End Function
'decrypt a string - wrapper without Base64 flag (True by default)
Public Function DecryptString(ByVal EncryptedString As String) As String
Return _DecryptString(EncryptedString, True)
End Function
'decrypt a string - wrapper with Base64 flag
Public Function DecryptString(ByVal EncryptedString As String, ByVal Base64 As Boolean) As String
Return _DecryptString(EncryptedString, Base64)
End Function
'encrypt a stream
Public Function EncryptStream(ByVal PlainStream As MemoryStream) As MemoryStream
Try
'open stream for encrypted data
Dim encStream As New MemoryStream()
'create Crypto Service Provider, set key, transform and crypto stream
Dim oCSP As New RijndaelManaged()
oCSP.Key = mbytKey
oCSP.IV = mbytIV
Dim ct As ICryptoTransform = oCSP.CreateEncryptor()
Dim cs As CryptoStream = New CryptoStream(encStream, ct, CryptoStreamMode.Write)
'get input stream into byte array
Dim byteArray() As Byte = PlainStream.ToArray()
'write input bytes to crypto stream and close up
cs.Write(byteArray, 0, PlainStream.Length)
cs.FlushFinalBlock()
cs.Close()
Return encStream
Catch ex As Exception
Return Stream.Null
End Try
End Function
'encrypt a string - wrapper without Base64 flag (True by default)
<Microsoft.SqlServer.Server.SqlFunction()> _
Function EncryptString(ByVal PlainText As String) As String
Return _EncryptString(PlainText, True)
End Function
''encrypt a string - wrapper with Base64 flag
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Function EncryptString2(ByVal PlainText As String, ByVal Base64 As Boolean) As String
Return _EncryptString(PlainText, Base64)
End Function
'calculates the hash of InputValue, returns a string
'- SHA1 hash is always 20 bytes (160 bits)
Public Function GetHashString(ByVal InputValue As String) As String
Try
Dim inputBytes() As Byte = BytesFromString(InputValue)
Dim hashValue() As Byte = New SHA1Managed().ComputeHash(inputBytes)
Return BytesToHexString(hashValue)
Catch ex As Exception
Return String.Empty
End Try
End Function
'returns True if hash of Passphrase matches HashValue
Public Function ValidPassword(ByVal Passphrase As String, ByVal HashValue As String) As Boolean
Return (GetHashString(Passphrase) = HashValue)
End Function
'internal string decryption
Private Function _DecryptString(ByVal EncryptedString As String, ByVal Base64 As Boolean) As String
Try
'put string in byte array depending on Base64 flag
Dim byteArray() As Byte
If Base64 Then
byteArray = Convert.FromBase64String(EncryptedString)
Else
byteArray = BytesFromString(EncryptedString)
End If
'create the streams, decrypt and return a string
Dim msEnc As New MemoryStream(byteArray)
Dim msPlain As MemoryStream = DecryptStream(msEnc)
Return BytesToString(msPlain.GetBuffer)
Catch ex As Exception
Return String.Empty
End Try
End Function
'internal string encryption
Private Function _EncryptString(ByVal PlainText As String, ByVal Base64 As Boolean) As String
Try
'put string in byte array
Dim byteArray() As Byte = BytesFromString(PlainText)
'create streams and encrypt
Dim msPlain As New MemoryStream(byteArray)
Dim msEnc As MemoryStream = EncryptStream(msPlain)
'return string depending on Base64 flag
If Base64 Then
Return Convert.ToBase64String(msEnc.ToArray)
Else
Return BytesToString(msEnc.ToArray)
End If
Catch ex As Exception
Return String.Empty
End Try
End Function
'returns a Unicode byte array from a string
Private Function BytesFromString(ByVal StringValue As String) As Byte()
Return (New UnicodeEncoding()).GetBytes(StringValue)
End Function
'returns a hex string from a byte array
Private Function BytesToHexString(ByVal byteArray() As Byte) As String
Dim sb As New StringBuilder(40)
Dim bValue As Byte
For Each bValue In byteArray
sb.AppendFormat(bValue.ToString("x2").ToUpper)
Next
Return sb.ToString
End Function
'returns a Unicode string from a byte array
Private Function BytesToString(ByVal byteArray() As Byte) As String
Return (New UnicodeEncoding()).GetString(byteArray)
End Function
'Return True when the file is available for writing
'- returns False if output file locked, for example
Private Function CheckWriteAccess(ByVal FileName As String) As Boolean
'2 second delay with 10,200
Dim iCount As Integer = 0 'retry count
Const iLimit As Integer = 10 'retries
Const iDelay As Integer = 200 'msec
While (iCount < iLimit)
Try
Dim fs As FileStream
fs = New FileStream(FileName, FileMode.Append, _
FileAccess.Write, FileShare.None)
fs.Close()
Return True
Catch ex As Exception
Thread.Sleep(iDelay)
Finally
iCount += 1
End Try
End While
Return False
End Function
End Class