Load Multiple Signed Assemblies

I am trying to load multiple strongly named assemblies into the same database which are signed with the same .snk file (signed in Visual Studio). I use the following code to create an asymmetric key and login as Books Online recommends:

CREATE ASYMMETRIC KEY SQLCLRKey FROM FILE = 'D:\dba\bin\Assembly.dll'

CREATE LOGIN CLRAssembler FROM ASYMMETRIC KEY SQLCLRKey

GRANT UNSAFE ASSEMBLY TO CLRAssembler

GRANT EXTERNAL ACCESS ASSEMBLY TO CLRAssembler

REVOKE CONNECT SQL FROM CLRAssembler

Do I need to create a new login and asymmetric key for each assembly I load? If so, do I need to sign each with a different key because its giving me an error message when I try to create 2 separate asymmetric keys/logins from 2 different assemblies which have been signed with the same .snk file.

The only way I've gotten everything to load properly is to create a separate key for each assembly and sign each, then create separate logins and asymmetric keys in the database.

Is this the only way to do this? Or am I missing something?

First of all I think you mean:

CREATE ASYMMETRIC KEY SQLCLRKey FROM EXECUTABLE FILE = 'D:\dba\bin\Assembly.dll'

FROM FILE = '...' requires a file that has both the public and private key in it, but an assembly has only the public key in it. Also you should be creating this key in the master database.

In order to use an asymmetic key to enable an assembly to be loaded the asymmetric key must be the master database and include public key, but the private key is not required. When FROM EXECUTABLE FILE = '...' is used the only the public key for the asymmetric key is saved. This key can be used to create a login to grant usafe assembly to. Then, assuming the use has the other appropriate permissions, any assembly signed with this key can be loaded with permission_set = unsafe. A single login is used to load all of the assemblies that are signed with the same key... you can't load the same asymmetric key more than once in the same database. You will have to be sure that Visual Studio is signing all your assemblies with the same key. If you are having to create a new login for each assembly it sounds like Visual Studio is creating a new key for each of these assemblies. When you go to the properties for your visual studio project browse for a common key, don't create a new one.

You can create the asymmetric key directly from the snk file that visual studio creates, for example if myKey.snk is the key pair created by visual studio then:

USE master
GO

CREATE ASYMMETRIC KEY [MyAssemblyKey] FROM FILE = 'c:\keys\myKey.snk'
-- remove the private key, no reason to leave it hanging around.
ALTER ASYMMETRIC KEY [MyAssemblyKey] REMOVE PRIVATE KEY

CREATE LOGIN [LoginMyAssemblyKey] FROM ASYMMETRIC KEY [Key MyAssemblyKey]
GRANT EXTERNAL ACCESS ASSEMBLY TO [LoginMyAssemblyKey]

GO

Once you have done this any assemblies signed with myKey.snk can be deployed from visual studio with unsafe permission set.

Dan

Dan

|||

My mistake. I did mean EXECUTABLE FILE.

I started out trying to sign them all with the same key and then loading them individually and dropping the key and login, however this was producing an error (which I can post once I get back into the office).

Do I need to load them in the same batch or script if I want to use the same login? Because I was running them separately.

If not, how do I specify the login to use? I tried using the AUTHORIZATION command with it and it threw a permissions error.

|||

I'm not sure what you mean when you say you drop the login after creating the assembly.

If you drop the login, or take away the login's USAFE ASSEMBLY permission, you will not be able to use the assembly even though even though it has been created. The login created with the assemblies key is required whenever any function from the assembly is used.

Dan

|||

You need only use CREATE ASSEMBLY. Authorization is used to specify an owner, it is not related to whether or not the assembly can be external acess or unsafe. If the assembly is being created WITH EXTERNAL_ACCESS or UNSAFE, SQL Server will use the key inside of the assembly to find the login created with that key, then check the permissions granted to that login. It, in effect, does this whenever a function from that assembly is used too.

Dan

Load from several CSV files

I have to load around 68 CSV files into one table. I have named the files
1.csv thru 68.csv. Is there a way I can don't have to make 68 packages to
load these. I am not very proficient with VBScriptTry using a global variable for the file name, a Dynamic Properties Task and
an ActiveX Script Task to programmatically loop through and change the name
of the input file for each import.
HTH
Jerry
"XXX" <sa@.nomail.com> wrote in message
news:uzU8pR7vFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I have to load around 68 CSV files into one table. I have named the files
>1.csv thru 68.csv. Is there a way I can don't have to make 68 packages to
>load these. I am not very proficient with VBScript
>|||You can concatenate the files and create a big one to be imported.
This is the help for dos command "copy".
*****
C:\>copy /?
Copies one or more files to another location.
COPY [/D] [/V] [/N] [/Y | /-Y] [/Z] [/A | /B ] source [/A | /B]
[+ source [/A | /B] [+ ...]] [destination [/A | /B]]
source Specifies the file or files to be copied.
/A Indicates an ASCII text file.
/B Indicates a binary file.
/D Allow the destination file to be created decrypted
destination Specifies the directory and/or filename for the new file(s).
/V Verifies that new files are written correctly.
/N Uses short filename, if available, when copying a file with a
non-8dot3 name.
/Y Suppresses prompting to confirm you want to overwrite an
existing destination file.
/-Y Causes prompting to confirm you want to overwrite an
existing destination file.
/Z Copies networked files in restartable mode.
The switch /Y may be preset in the COPYCMD environment variable.
This may be overridden with /-Y on the command line. Default is
to prompt on overwrites unless COPY command is being executed from
within a batch script.
To append files, specify a single file for destination, but multiple files
for source (using wildcards or file1+file2+file3 format).
*****
AMB
"XXX" wrote:

> I have to load around 68 CSV files into one table. I have named the files
> 1.csv thru 68.csv. Is there a way I can don't have to make 68 packages to
> load these. I am not very proficient with VBScript
>
>

Listbox

I have a report with a single parameter, named param1. The parameter is
a list box that accepts multiple values.
When I select a single value from a listbox, the report works fine, But
when I select more than one value, the stored procedure call fails
saying '[Query execution failed for data set 'XXX' Must decalare the
variable '@.param1'.]'
I initially assumed that the multiple values would be passed to my SP
in the form of a single comma-delimited varchar, but this does not
seems to be the case. How can I set up the stored procedure call to
take multiple values from a listbox? Do I need to do something special
in the SP to process the multiple values?Hi,
you will have to write your query like this here:
WHERE SomeColumn IN (@.parametername)
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||It is passed the way you suppose. But, try calling your stored procedure
yourself (not from Reporting Services). Manually pass it a comma separated
string for the parameter. It won't work. This is a stored procedure issue,
not a Reporting Services issue. If you have the query defined in RS you can
do like this: select * from sometable where somefield in (@.MyParam) but you
cannot do this if that statement is in a stored procedure.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
select * from sometable where somefield in (select str from
charlist_to_table(@.MyParam,Default))
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"melishbd" <melissa@.hbdc.com> wrote in message
news:1169581326.626144.35060@.v45g2000cwv.googlegroups.com...
>I have a report with a single parameter, named param1. The parameter is
> a list box that accepts multiple values.
> When I select a single value from a listbox, the report works fine, But
> when I select more than one value, the stored procedure call fails
> saying '[Query execution failed for data set 'XXX' Must decalare the
> variable '@.param1'.]'
> I initially assumed that the multiple values would be passed to my SP
> in the form of a single comma-delimited varchar, but this does not
> seems to be the case. How can I set up the stored procedure call to
> take multiple values from a listbox? Do I need to do something special
> in the SP to process the multiple values?
>

List All Instances of SQL Server

Hi all,
I am trying to generate a list of all available SQL Servers (named instances
and all) on a network. I have seen over and over again to use SQLDMO or isq
l
-L.
The problem that I am having is that these methods only seem to want to
return one instance from each computer.
ex. Computer "Main" has
Main
Main\Instance1
Main\Instance2
These methods are only returning "Main" in the list and not the rest of the
named instances. I have tried everything I can think of, including making
sure the protocols "named pipes" and "TCP/IP" are activated for each
instance. No matter what I have tried these Names won't return. I have
resorted to reading the registry to get get the instances for the local
computer from the "InstalledInstances" key.
This method is fine for the local computer but won't work for network
computers.
Do any of you have any suggestions for returning a complete list of all
available servers?
Is there something I'm doing wrong or missing?
Also I would like to return a list of local servers when the network cable
is unplugged. Do you have a suggestion for how to solve this problem?
Thanks for any help,
KenKen,
I was trying to do the same thing. Only thing I found that returned my
Instances was the following.
Only thing is not sure how well this will work in a network environment
since this is reading the registry. If you figure how to do it on a network
or a different way let me know your solution.
RegistryKey objInstances = Registry.LocalMachine;
objInstances = objInstances.OpenSubKey(@."SOFTWARE\Microsoft\Microsoft SQL
Server\Instance Names\SQL", true);
foreach (string Keyname in objInstances.GetValueNames())
{
tvTableInfo.Nodes.Add(Keyname);
}
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:E4BE412C-A98C-4522-B902-5AC1398F2F72@.microsoft.com...
> Hi all,
> I am trying to generate a list of all available SQL Servers (named
> instances
> and all) on a network. I have seen over and over again to use SQLDMO or
> isql
> -L.
> The problem that I am having is that these methods only seem to want to
> return one instance from each computer.
> ex. Computer "Main" has
> Main
> Main\Instance1
> Main\Instance2
> These methods are only returning "Main" in the list and not the rest of
> the
> named instances. I have tried everything I can think of, including making
> sure the protocols "named pipes" and "TCP/IP" are activated for each
> instance. No matter what I have tried these Names won't return. I have
> resorted to reading the registry to get get the instances for the local
> computer from the "InstalledInstances" key.
> This method is fine for the local computer but won't work for network
> computers.
> Do any of you have any suggestions for returning a complete list of all
> available servers?
> Is there something I'm doing wrong or missing?
> Also I would like to return a list of local servers when the network cable
> is unplugged. Do you have a suggestion for how to solve this problem?
> Thanks for any help,
> Ken|||Thanks, but that is essentially what I accomplished by reading the
"NamedInstances" key. That type of situation works well for the local
computer but like you said, it won't work for network machines.
Thanks for the input though.
"JP" wrote:

> Ken,
> I was trying to do the same thing. Only thing I found that returned my
> Instances was the following.
> Only thing is not sure how well this will work in a network environment
> since this is reading the registry. If you figure how to do it on a networ
k
> or a different way let me know your solution.
> RegistryKey objInstances = Registry.LocalMachine;
> objInstances = objInstances.OpenSubKey(@."SOFTWARE\Microsoft\Microsoft SQL
> Server\Instance Names\SQL", true);
> foreach (string Keyname in objInstances.GetValueNames())
> {
> tvTableInfo.Nodes.Add(Keyname);
> }
>
>
> "Ken" <Ken@.discussions.microsoft.com> wrote in message
> news:E4BE412C-A98C-4522-B902-5AC1398F2F72@.microsoft.com...
>
>|||I am using sql-dmo to get list of instances. I have the same problem. But
i figured out that when i disable my local firewall application sql-dmo
returns all SQL Server instances. So try to temporarily turn off your
firewalls.
On Fri, 19 Aug 2005 02:22:04 +0300, Ken <Ken@.discussions.microsoft.com>
wrote:

> Hi all,
> I am trying to generate a list of all available SQL Servers (named
> instances
> and all) on a network. I have seen over and over again to use SQLDMO or
> isql
> -L.
> The problem that I am having is that these methods only seem to want to
> return one instance from each computer.
> ex. Computer "Main" has
> Main
> Main\Instance1
> Main\Instance2
> These methods are only returning "Main" in the list and not the rest of
> the
> named instances. I have tried everything I can think of, including
> making
> sure the protocols "named pipes" and "TCP/IP" are activated for each
> instance. No matter what I have tried these Names won't return. I have
> resorted to reading the registry to get get the instances for the local
> computer from the "InstalledInstances" key.
> This method is fine for the local computer but won't work for network
> computers.
> Do any of you have any suggestions for returning a complete list of all
> available servers?
> Is there something I'm doing wrong or missing?
> Also I would like to return a list of local servers when the network
> cable
> is unplugged. Do you have a suggestion for how to solve this problem?
> Thanks for any help,
> Ken|||That was it.
I was using a software firewall and had disabled that to see if it was the
problem, and I had assumed that the windows firewall was disabled(as I had
previously disabled it). Once I disabled the windows firewall all instances
started showing up.
Thanks for the response,
Ken
"Igor Solodovnikov" wrote:

> I am using sql-dmo to get list of instances. I have the same problem. But
> i figured out that when i disable my local firewall application sql-dmo
> returns all SQL Server instances. So try to temporarily turn off your
> firewalls.
> On Fri, 19 Aug 2005 02:22:04 +0300, Ken <Ken@.discussions.microsoft.com>
> wrote:
>
>