DRAG DROP
You plan to create a custom aggregation function named Function1.
You plan to deploy Function1 to SQL Server 2012.
You need to ensure that Function1 can access a web service. The solution must minimize
the number of changes made to the database.
You create a Microsoft .NET Framework class that contains the definition of Function1.
You upload a certificate to SQL Server.
What three tasks should you perform next?
To answer, move the appropriate actions from the list of actions to the answer area and
arrange them in the correct order.
Answer: See the explanation.
Explanation:
Box 1: Modify the TRUSTWORTHY property of the databaseBox 2: Execute the CREATE ASSEMBLY statement.
Box 3: Execute the CREATE AGGREGATE statement.Note:
* TRUSTWORTHY CREATE signature
The TRUSTWORTHY property indicates whether the instance of SQL Server trusts the
database and the contents within it.
* CREATE AGGREGATE
Creates a user-defined aggregate function whose implementation is defined in a class of an
assembly in the .NET Framework. For the Database Engine to bind the aggregate function
to its implementation, the .NET Framework assembly that contains the implementation must
first be uploaded into an instance of SQL Server by using a CREATE ASSEMBLY
statement.
* Example:
ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON
GO
CREATE ASSEMBLY [CLR.Utilities] FROM ‘C:\Path\To\File\CLR.Utilities.dll’ WITH
PERMISSION_SET = UNSAFE
GO
CREATE AGGREGATE [dbo].[Concatenate] (@input nvarchar(max)) RETURNS
nvarchar(max)
EXTERNAL NAME [CLR.Utilities].[CLR.Utilities.Concatenate]
GO
Similar question with SQL 2014 environment, answer is to create assembly and then sign it using certificate!
Can somebody please clarify which is correct?
0
0
I think here (in sql 2012) correct answer is to use a certificate too.
In question is written that certificate was uploaded into to SQL Server.
https://msdn.microsoft.com/en-us/library/ms345106.aspx
We recommend that the TRUSTWORTHY Database Property on a database not be set to ON only to run common language runtime (CLR) code in the server process.
The signing is done i visual studio, so it must be done before creating assembly in sql server.
0
0
Agree with jml.The best practice is to use asymetric key or certificate.
see complete steps required here:
http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/10/29/deploying-sql-clr-assembly-using-asymmetric-key.aspx
http://www.manjuke.com/2011/11/deployuse-assemblies-which-require.html
0
0
1. Use certificate to add digital signature to the assembly
2. Execute the create assembly statement
3. Execute create aggregate statement
1
0
Agree. This als minimizes
the number of changes made to the database
0
0
1 Use the certificate to add a digital signature to the assembly
2 Execute the CREATE ASSEMBLY statement
3 EXECUTE THE CREATE AGGREGATE statement
1
0
Is it correct order?
1. Execute the CREATE ASSEMBLY statement
2. Use the certificate to add a digital signature to the assembly
3. EXECUTE THE CREATE AGGREGATE statement
0
0