Hide database names from unauthroized users in SSMS

If a user is not authorized to see a database can I exclude that database from even appearing in Management Studio for that user

TIA,

Barkingdog

Hi,

you would need to revove the permission 'VIEW ANY DATABASE' from the role PUBLIC.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I found the server-level permission "View any database", denied it to the test user, and found exactly that. The user couldn't see (under SSMS) any databases whatsoever including the one he is the DBO of! How can I assign him permissions to see that one database but no others in the SSMS tree?

TIA,

Barkingdog

P.S. The evidence is if I removed that permission from "public" then no one -- maybe even the sa? - could see any databases in SSMS.!

|||

How are you making the user an owner? Are you making him a member of db_owner or are you using the alter authorization statement to make the user the actual owner of the database?

You can tell by looking at the owner_sid in sys.databases - the sid should point to the owning login. If the login is the owner of the database, denying that user view any database will not prevent him from viewing the database in sys.databases or Management Studio.

Jack Richins
SDE Sql Server

|||

I made him a member of the db_owner.

When I run

sp_helpuser <DBName>

it returns x01 as the SID for the username dbo.

When I run

use DBName

select * from sys.databases

it returns.......

0x010500000000000515000000AAB950D6A501....

for the owner sid of the database.

So the user is not the DBO (though he belongs to that role).

Then back to my original question. How can I deny a user from seeing any databses in the SSMS tree that he does not have permission to access?

TIA,

Barkingdog

|||

DENY VIEW ANY DATABASE to the test login. This prevents the user from seeing any databases for which the user is not the DBO. You can make the user a DBO by using ALTER AUTHORIZATION DATABASE::<database> TO <login>. Being a member of db_owner is not sufficient to see the database if "view any database" was denied.

This is not very clean and may not work for you if you can't make this user login the actual DBO of the database you want him to administer or have multiple logins you want to secure in this manner as only a single login can be the actual DBO of a database.

This is something we're investigating for future improvements. So if you could provide some details on what you would like this login to be able to do and what you don't want the login to see or do, it could help us better solve your problems as we design future versions of SQL Server. Also whether you use SQL Logins or Windows Integrated Logins - how many different logins you'd like to secure in this way, and anything else you think might be relevant.

Thanks,
Jack Richins
SDE SQL Server

|||

Dear Jack,

This whole issue took me by surprise. In our shop we have three teams of developers and all the development datbases are in a single sql instance. One of the group leaders asked me if there is any way to prevent those not in his group from even seeing the database display name under SSMS and sql tools (e.g. query editor, via OLEDB). I don't know the reason for his concern -- developers outside his group are not authorized to even view his databases but from a security perspective it seems reasonable: If a user does not have access to a databse, why should the database even "exist" for that user. This applies regardless of the form of Windows authentication used.

TIA,

Barkingdog

|||

Thanks, this isn't a scenario we'd been discussing so I'm glad you brought this up. I'm sorry we don't have a solution for you now, but hopefully in the future we can do something to enable this.

Thanks,
Jack Richins
SDE SQL Server

|||Although it blocks users from seeing other databases in the SMSS database dropdown, as the user I can still query sys.databases and see everything and I have used the deny view any database to public. In a hosting environment, this just won't be acceptable. In sql 2000, I just created a sysdatabases view that looked at your credentials and passed what you had rights to. Can't do that in 2005, but definately need this.|||

The main issue here is that determining what databases a user can see is an expensive check, as the permissions to access the database are stored in the database itself, so verifying them requires a database access. This prevents a general solution from being available through SSMS - some customers manage hundreds of databases on a single server, which would make such checks prohibitive.

You can still write a view in SQL Server 2005 that could filter rows using "has_dbaccess() = 1" as a predicate, but you should only do this if you have a limited number of databases, as it would be too expensive otherwise.

I also want to add that we're aware of this request and we're looking at ways to address it.

Thanks
Laurentiu

Hide database names from unauthroized users in SSMS

If a user is not authorized to see a database can I exclude that database from even appearing in Management Studio for that user

TIA,

Barkingdog

Hi,

you would need to revove the permission 'VIEW ANY DATABASE' from the role PUBLIC.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

I found the server-level permission "View any database", denied it to the test user, and found exactly that. The user couldn't see (under SSMS) any databases whatsoever including the one he is the DBO of! How can I assign him permissions to see that one database but no others in the SSMS tree?

TIA,

Barkingdog

P.S. The evidence is if I removed that permission from "public" then no one -- maybe even the sa? - could see any databases in SSMS.!

|||

How are you making the user an owner? Are you making him a member of db_owner or are you using the alter authorization statement to make the user the actual owner of the database?

You can tell by looking at the owner_sid in sys.databases - the sid should point to the owning login. If the login is the owner of the database, denying that user view any database will not prevent him from viewing the database in sys.databases or Management Studio.

Jack Richins
SDE Sql Server

|||

I made him a member of the db_owner.

When I run

sp_helpuser <DBName>

it returns x01 as the SID for the username dbo.

When I run

use DBName

select * from sys.databases

it returns.......

0x010500000000000515000000AAB950D6A501....

for the owner sid of the database.

So the user is not the DBO (though he belongs to that role).

Then back to my original question. How can I deny a user from seeing any databses in the SSMS tree that he does not have permission to access?

TIA,

Barkingdog

|||

DENY VIEW ANY DATABASE to the test login. This prevents the user from seeing any databases for which the user is not the DBO. You can make the user a DBO by using ALTER AUTHORIZATION DATABASE::<database> TO <login>. Being a member of db_owner is not sufficient to see the database if "view any database" was denied.

This is not very clean and may not work for you if you can't make this user login the actual DBO of the database you want him to administer or have multiple logins you want to secure in this manner as only a single login can be the actual DBO of a database.

This is something we're investigating for future improvements. So if you could provide some details on what you would like this login to be able to do and what you don't want the login to see or do, it could help us better solve your problems as we design future versions of SQL Server. Also whether you use SQL Logins or Windows Integrated Logins - how many different logins you'd like to secure in this way, and anything else you think might be relevant.

Thanks,
Jack Richins
SDE SQL Server

|||

Dear Jack,

This whole issue took me by surprise. In our shop we have three teams of developers and all the development datbases are in a single sql instance. One of the group leaders asked me if there is any way to prevent those not in his group from even seeing the database display name under SSMS and sql tools (e.g. query editor, via OLEDB). I don't know the reason for his concern -- developers outside his group are not authorized to even view his databases but from a security perspective it seems reasonable: If a user does not have access to a databse, why should the database even "exist" for that user. This applies regardless of the form of Windows authentication used.

TIA,

Barkingdog

|||

Thanks, this isn't a scenario we'd been discussing so I'm glad you brought this up. I'm sorry we don't have a solution for you now, but hopefully in the future we can do something to enable this.

Thanks,
Jack Richins
SDE SQL Server

|||Although it blocks users from seeing other databases in the SMSS database dropdown, as the user I can still query sys.databases and see everything and I have used the deny view any database to public. In a hosting environment, this just won't be acceptable. In sql 2000, I just created a sysdatabases view that looked at your credentials and passed what you had rights to. Can't do that in 2005, but definately need this.|||

The main issue here is that determining what databases a user can see is an expensive check, as the permissions to access the database are stored in the database itself, so verifying them requires a database access. This prevents a general solution from being available through SSMS - some customers manage hundreds of databases on a single server, which would make such checks prohibitive.

You can still write a view in SQL Server 2005 that could filter rows using "has_dbaccess() = 1" as a predicate, but you should only do this if you have a limited number of databases, as it would be too expensive otherwise.

I also want to add that we're aware of this request and we're looking at ways to address it.

Thanks
Laurentiu

|||I have tried changing the view any database property, which works, but the user cannot see the databases that they own... is there something else that needs to be done for them to see these databases and no others?|||

This is what I ended up doing:

--SQL 2005

If @.@.version like 'Microsoft SQL Server 2005%'

Begin -- Create Login only if it doesn't exist:

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = @.Username)

Begin

Set @.dsql ='create login ' + @.Username + ' with password = N'''+ REPLACE(@.password, '''', '''''') + ''', DEFAULT_DATABASE=' + QUOTENAME(@.DBName) + ', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'

execute (@.dsql)

set @.dsql = 'use master; GRANT CONNECT SQL TO ' + QUOTENAME(@.DBName)

execute( @.dsql )

End

--Deny ability to see anything other than master, tempdb and user database.

Set @.dsql = 'use master; deny VIEW any DATABASE to ' + @.Username

--Print @.dsql

execute (@.dsql)

End

--Add user as dbo to database.

Set @.dsql = @.DBName + '.dbo.sp_changedbowner ' + @.Username

--Print @.dsql

execute (@.dsql)

But, I don't think you can grant multiple user's to DBO, so you will only have one user that can see master, temp, and their database.

-Chuck Lathrope

Hide database names from unauthroized users in SSMS

If a user is not authorized to see a database can I exclude that database from even appearing in Management Studio for that user

TIA,

Barkingdog

Hi,

you would need to revove the permission 'VIEW ANY DATABASE' from the role PUBLIC.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I found the server-level permission "View any database", denied it to the test user, and found exactly that. The user couldn't see (under SSMS) any databases whatsoever including the one he is the DBO of! How can I assign him permissions to see that one database but no others in the SSMS tree?

TIA,

Barkingdog

P.S. The evidence is if I removed that permission from "public" then no one -- maybe even the sa? - could see any databases in SSMS.!

|||

How are you making the user an owner? Are you making him a member of db_owner or are you using the alter authorization statement to make the user the actual owner of the database?

You can tell by looking at the owner_sid in sys.databases - the sid should point to the owning login. If the login is the owner of the database, denying that user view any database will not prevent him from viewing the database in sys.databases or Management Studio.

Jack Richins
SDE Sql Server

|||

I made him a member of the db_owner.

When I run

sp_helpuser <DBName>

it returns x01 as the SID for the username dbo.

When I run

use DBName

select * from sys.databases

it returns.......

0x010500000000000515000000AAB950D6A501....

for the owner sid of the database.

So the user is not the DBO (though he belongs to that role).

Then back to my original question. How can I deny a user from seeing any databses in the SSMS tree that he does not have permission to access?

TIA,

Barkingdog

|||

DENY VIEW ANY DATABASE to the test login. This prevents the user from seeing any databases for which the user is not the DBO. You can make the user a DBO by using ALTER AUTHORIZATION DATABASE::<database> TO <login>. Being a member of db_owner is not sufficient to see the database if "view any database" was denied.

This is not very clean and may not work for you if you can't make this user login the actual DBO of the database you want him to administer or have multiple logins you want to secure in this manner as only a single login can be the actual DBO of a database.

This is something we're investigating for future improvements. So if you could provide some details on what you would like this login to be able to do and what you don't want the login to see or do, it could help us better solve your problems as we design future versions of SQL Server. Also whether you use SQL Logins or Windows Integrated Logins - how many different logins you'd like to secure in this way, and anything else you think might be relevant.

Thanks,
Jack Richins
SDE SQL Server

|||

Dear Jack,

This whole issue took me by surprise. In our shop we have three teams of developers and all the development datbases are in a single sql instance. One of the group leaders asked me if there is any way to prevent those not in his group from even seeing the database display name under SSMS and sql tools (e.g. query editor, via OLEDB). I don't know the reason for his concern -- developers outside his group are not authorized to even view his databases but from a security perspective it seems reasonable: If a user does not have access to a databse, why should the database even "exist" for that user. This applies regardless of the form of Windows authentication used.

TIA,

Barkingdog

|||

Thanks, this isn't a scenario we'd been discussing so I'm glad you brought this up. I'm sorry we don't have a solution for you now, but hopefully in the future we can do something to enable this.

Thanks,
Jack Richins
SDE SQL Server

|||Although it blocks users from seeing other databases in the SMSS database dropdown, as the user I can still query sys.databases and see everything and I have used the deny view any database to public. In a hosting environment, this just won't be acceptable. In sql 2000, I just created a sysdatabases view that looked at your credentials and passed what you had rights to. Can't do that in 2005, but definately need this.|||

The main issue here is that determining what databases a user can see is an expensive check, as the permissions to access the database are stored in the database itself, so verifying them requires a database access. This prevents a general solution from being available through SSMS - some customers manage hundreds of databases on a single server, which would make such checks prohibitive.

You can still write a view in SQL Server 2005 that could filter rows using "has_dbaccess() = 1" as a predicate, but you should only do this if you have a limited number of databases, as it would be too expensive otherwise.

I also want to add that we're aware of this request and we're looking at ways to address it.

Thanks
Laurentiu

Hidding report before deployment?

Hi,
does it possible to set the "hidden in list view" option at design time of
the report (or any other objects) in Visual studio prior a deployment?
Because I've a lot of reports and images to send to RS, but some of these
are hidden by default. My front end interface hidde these reports to the
user.
Thanks.
Jerome.No, not from report designer. If you use a script to deploy your reports,
you can manipulate these properties.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
news:%23M4yhsJWEHA.4056@.TK2MSFTNGP11.phx.gbl...
> Hi,
> does it possible to set the "hidden in list view" option at design time of
> the report (or any other objects) in Visual studio prior a deployment?
> Because I've a lot of reports and images to send to RS, but some of these
> are hidden by default. My front end interface hidde these reports to the
> user.
> Thanks.
> Jerome.
>|||So, can I add custom information in the RDL file?
My idea is:
Creating a program (or VS add in) to add some deployment option by report,
then my export application will read each report to retrieve my custom
information to know if the report must be hidden or not.
What do you think about this?
or
have you a sample script to deploy RS repotrs?
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> a écrit dans le
message de news:%23CeCTIKWEHA.4032@.TK2MSFTNGP11.phx.gbl...
> No, not from report designer. If you use a script to deploy your reports,
> you can manipulate these properties.
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
>
> "Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
> news:%23M4yhsJWEHA.4056@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > does it possible to set the "hidden in list view" option at design time
of
> > the report (or any other objects) in Visual studio prior a deployment?
> >
> > Because I've a lot of reports and images to send to RS, but some of
these
> > are hidden by default. My front end interface hidde these reports to the
> > user.
> >
> > Thanks.
> >
> > Jerome.
> >
> >
>|||There is a sample publish script included with the product that you could
use.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:%23mfVPiYWEHA.1152@.TK2MSFTNGP09.phx.gbl...
> So, can I add custom information in the RDL file?
> My idea is:
> Creating a program (or VS add in) to add some deployment option by report,
> then my export application will read each report to retrieve my custom
> information to know if the report must be hidden or not.
> What do you think about this?
> or
> have you a sample script to deploy RS repotrs?
>
> "Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> a écrit dans le
> message de news:%23CeCTIKWEHA.4032@.TK2MSFTNGP11.phx.gbl...
>> No, not from report designer. If you use a script to deploy your
>> reports,
>> you can manipulate these properties.
>> -Lukasz
>>
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>>
>> "Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
>> news:%23M4yhsJWEHA.4056@.TK2MSFTNGP11.phx.gbl...
>> > Hi,
>> >
>> > does it possible to set the "hidden in list view" option at design time
> of
>> > the report (or any other objects) in Visual studio prior a deployment?
>> >
>> > Because I've a lot of reports and images to send to RS, but some of
> these
>> > are hidden by default. My front end interface hidde these reports to
>> > the
>> > user.
>> >
>> > Thanks.
>> >
>> > Jerome.
>> >
>> >
>>
>sql

Hidden max number of columns in Management Studio?

Hi,
The following is observed in the SQLServer 2005 Management Studio Visual
Editor:
Open table (Right Click option on a table) fails when I try it on a table
with lots of columns in it > 820 columns! Currently the table has no rows
(empty table). The same table opens fine with sql 2000 enterprise manager.
This table has been migrated/imported from sql 2000 without any errors! Other
imported tables with less number of columns can be opened in the visual
editor!
Wondering is there a hidden limit in terms of number of columns with
SQLserver 2005 Management Studio (Grid) Visual Editor?
I get SQL Executaion error
Error Source: Microsoft.VisualStudio.DataTools
Exception has been thrown by the target of an invocation
Is this a known behavior/limitation/bug? Is there a workaround?
Thanks
Kan,
See
http://www.sql-server-performance.co...ID=9016鵬
You may wish to report this as a bug at the suggested URL
http://lab.msdn.microsoft.com/produc...k/default.aspx
(also in the thread).
Steve Kass
Drew University
Kan wrote:

>Hi,
>The following is observed in the SQLServer 2005 Management Studio Visual
>Editor:
>Open table (Right Click option on a table) fails when I try it on a table
>with lots of columns in it > 820 columns! Currently the table has no rows
>(empty table). The same table opens fine with sql 2000 enterprise manager.
>This table has been migrated/imported from sql 2000 without any errors! Other
>imported tables with less number of columns can be opened in the visual
>editor!
>Wondering is there a hidden limit in terms of number of columns with
>SQLserver 2005 Management Studio (Grid) Visual Editor?
>I get SQL Executaion error
>Error Source: Microsoft.VisualStudio.DataTools
>Exception has been thrown by the target of an invocation
>Is this a known behavior/limitation/bug? Is there a workaround?
>Thanks
>
>

hidden fields rendering on server only!

I have a report with a matrix in which I conditionally hide one of the data
columns. This is working properly in preview on my development studio but
when I deploy to the report server it is showing these hidden data columns
when it is not supposed to - if I export the rendered report from the server
... it exports to pdf and excel with the data columns properly hidden - so
how can I fix this ... I cant understand what is happening. Please help as
release date is approaching. Thank youUpdate on this - I put the same conditional expressions on the border
property of the columns I want hidden and also the background color of the
column that gets conditionally colored - this seems to work although it
doesnt explain why it worked w/out that in dev studio AND why it exported
correctly - the only problem being on the server in html format. Still would
like to know why because if I had known that then I could have saved a lot of
time. Thanks
"MJT" wrote:
> I have a report with a matrix in which I conditionally hide one of the data
> columns. This is working properly in preview on my development studio but
> when I deploy to the report server it is showing these hidden data columns
> when it is not supposed to - if I export the rendered report from the server
> ... it exports to pdf and excel with the data columns properly hidden - so
> how can I fix this ... I cant understand what is happening. Please help as
> release date is approaching. Thank you

hi, i need help on SQL, thanks

I'm doing a shopping cart using SQL Express and Visual Studio Web Developer on C#, ASP.NET

I recieved error when adding a order:

The variable name '@.oid' has already been declared. Variable names must be unique within a query batch or stored procedure.

The codes are:
comm = new SqlCommand("SELECT IDENT_CURRENT('Orders') as NewOrderID ");

comm.Connection = conn;
comm.Transaction = myTrans;

OrderID = Convert.ToInt32(comm.ExecuteScalar());

foreach (CartItem i in o.ItemList)
{
comm.CommandText = "INSERT INTO OrderDetail(OrderID, ProductID,Quantity, UnitPrice)VALUES (@.oid, @.pid, @.qty, @.price)";

comm.Parameters.AddWithValue("@.oid", OrderID);
comm.Parameters.AddWithValue("@.pid", i.ProductID);
comm.Parameters.AddWithValue("@.qty", i.Quantity);
comm.Parameters.AddWithValue("@.price", i.UnitPrice);
comm.Connection = conn;
comm.Transaction = myTrans;
comm.ExecuteNonQuery();
}

It seems that i can't add records into database with multiple loop.

Thanks in advance.

Well you want to only call once the insert for multiple items, that will optimize the code a little bit more.

Use TableAdapters, On the project right click and select Add New Item, select a DataSet and create a method to add multiple items!

|||

Try to clear parameters after each insert.

Add this to the end of your code block:

...........

comm.ExecuteNonQuery();

comm.Parameters.Clear();

}

|||

Thanksalbertpascual,

I'm not sure about using a TableAdapter, but does declaring the parameters outside the loop works?

I tried adding a dataset but with my limited 1month knowledge, I don't know how to complete the wizard or codes for the dataset.

if changing my existing codes works, it will be great.

thanks again,

|||

Hey, thanks alot Limno!!

but adding this sweet and simple "comm.Parameters.Clear();" it works..

thanks!!

Here is the answer FOLKS and it WORKS!!!

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 [Builtin\Administrators].

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) TriedBuiltin\Administrators 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

Who is the owner of the database ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Thanks for the reply.

I am running with Windows Authentication at the present moment , under the ID Administrator1 (running under windows Vista.(still would like to get reporting services to run ) . I did read the article on "Opening up the Administrator ID in Vista and installing SQL Server 2005 under that ID."

Re my current problem....

I managed to get it to deploy. I assigned Administrator1 as an "owner" of the database and gave the ID security administrator rights. I did the same with Builtin/Administrator.

I found something very interesting. I am able to deploy CLR stored precedures from the Visual Studio environment but not CLR functions. (EXTERNAL ACCESS) In order to deply functions I must compile the Dll and create SQL to CREATE the ASSEMBLYfrom within the SQL Server Environment. Any ideas why.?

Once again thanks for the help? Very much appreciated.

Steve

|||

OK Folks this took me about 3 hours to do BUT I have resolved the problem and it WORKS!!!

BTW I am using SQL Server 2005 Standard and Visual Studio.NET Enterprise

First create a Strong key as described in“The secret of strong naming”

DO NOTDODELAY SIGNING !!!!!!

http://www.ondotnet.com/pub/a/dotnet/2003/04/28/strongnaming.html

Sn – k MyKeyFile.snk

FIND OUT WHERE THE SNK FILE WAS COMPILED.

Go into VS2005 .NET and under project / properties in the signing page click the Sign the Assembly check box and browse to find MyKeyFile.snk

The database page

Mark the assembly for external access and leave the owner blank

Now we must go to the article

http://sqljunkies.com/WebLog/ktegels/archive/2006/08/14/safetydancelite.aspx

The text below comes from this article....

A kinder, lighter Safety Dance for the SQLCLR

A few months ago, I published a step-by-step procedure for using certificate to sign an assembly so an external access or unsafe assembly could be run without having to set the hosting database to trustworthy. The major problem with that process was its weight: a lot of steps and some still requirements in terms of certificate management. Turns out that yes, there is an easier way to make it work.

Part one -- preparing the master database

1. As needed, start a series of queries against the MASTER database.

2. Create the target database (meaning execute a CREATE DATABASE query).

3. Code and compile the assembly you want to deploy. Make sure you sign the assembly with a strong name key file. This can be done with a PFX file generated by Visual Studio.

4. Create an asymmetric key from from the compiled assembly using a statement like this: create asymmetric key <key_name> from executable file = '<path_to_dll_file>'

USE Master

GO

create asymmetric key imageskeyFile from executable file = 'C:\YEAR 2007\Images\Images\bin\Debug\Images.dll'

5. Create a login based on that asymmetric key using a statement like this: create login <login_name> from asymmetric key <key_name>

Use Master

Go

create login ImageMaker from asymmetric key imageskeyFile

6. Grant that login the right to create either or both an unsafe or external access assembly (as needed) using: grant unsafe assembly to <login_name>

Use Master

Go

grant EXTERNAL ACCESS assembly to ImageMaker

Part two -- preparing the hosting database

1. As needed, start a series of queries against the desired user database.

2. Create a user in that database mapped to the login created in part, step 5. (e.g. create user <db_user> from login <login_name>)

3. Give that user the right to catalog an assembly, e.g.: grant create assembly to <db_user>

4. Catalog the desired assembly using the now trusted asymmetric key with a statement like: create assembly <assembly_name> authorization <db_user> from '<path_to_dll_file>' with permission_set = unsafe or external access as shown below

SET QUOTED_IDENTIFIER OFF

USE YEAR2007

GO

CREATE ASSEMBLY Images

AUTHORIZATION ImageMaker

FROM "C:\YEAR 2007\Images\Images\bin\Debug\Images.dll"

WITH PERMISSION_SET = EXTERNAL_ACCESS

GO

Help: I can open my database in Management Studio, but can't connect it in Integration Services

Received error tip:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I'm using the Evaluation Edition of SQL Server 2005, Windows XP SP2 Home Edition.

I have disabled the firewall.

You need to enable remote connections:

http://www.sqlserver2005.de/Sharedfiles/remoteconnection.jpg

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Yes, I have enable remote connections, but still wrong.

Can the Home Edition of Windows XP support SQL Server?

|||

Hi, only the developer edition and the express edition are supposed to work on Windows XP Home (as of the offical system requirements, didn′t tested that out yet). Let me hear some feedback if that worked for you.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I have installed SQL Server Evaluation Edition on Windows XP Home Edition. It can work. In the Management Studio, the database can be opened and executed. The only one problem is that in the reporting services, the database can't be connetcted.

HELP: Failed to create the task. (Microsoft Visual Studio)

I created my first SSIS project in BI Dev Studio but get the following error when trying to add a data flow task to the Control container:

==================================

Failed to create the task. (Microsoft Visual Studio)

===================================

Value does not fall within the expected range. (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Executables.Add(String moniker)
at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)

Any advice greatly appreciated..

cheers,
jordan

What build are you on? June CTP by chance?|||I just installed the June CPT and I am gettting this same error. Any ideas?

Thanks in advance.|||

Here's the entire error message:

===================================

Value does not fall within the expected range. (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Executables.Add(String moniker)
at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.CreateExecutable(String moniker, IDTSSequence container, String name)

|||Yes it is June CTP Developer Edition.|||I also couldn't use the SQL Server Import and Export Wizard. The error that I get:

The SSIS data flow task could not be created. Verify that DTSPipeline.dll is available and registered. The wizard cannot continue and it will terminate.

Value does not fall within the expected range. (Microsoft.SQLServer.DTSRuntimeWrap)|||Hmmm, very odd. It sounds as though you have a corrupted installation. Is this the first time you've installed SQL Server on that machine, IOW is it a clean install? You might try to regsvr32 DTSPipeline.dll. Then try again. It appears that it's just not registered.|||

I have tried uninstalling and reinstalling the CPT 4 times. The last time I did a regclean, deleted left over directories and anything else I could thing of the make the install clean.

One thing I should mention is that I had the VSS.Net 2005 Beta 2 on my machine before I installed the SQL Server 2005 CPT and I had to remove it in order to go forward with the SQL install. Not sure if that left something’s behind. I did use the SQL removal tools to get things off my machine the last couple of times, but not the first. The first time I used the Add/Remove Programs applet, and those uninstalls failed, but the option to remove through the applet disappeared afterward.

Very frustrating. I have installed this on another machine without the problems; it's just that that machine is not my main workstation (and not portable).

I will try the regsvr32 suggestion and regasm on some of the other libraries if that doesn't help.

Thanks!

|||Tried that, no luck.

Any other suggestions?

Thanks!|||I did a search on my local hard drives and couldn't even find DTSPipeline.dll. The CTP version that we have were downloaded from MSDN subscribers download web site.

cheers,
j.|||Has there been any resolve to this error at all? I am close to giving up on the CTP entirely.

Any help would be greatly appreciated.|||Where are you getting the install from? What SKU are you installing? The DtsPipeline.dll should be installed on all SKUs. Are you installing Integration Services? Is there a Microsoft SQL Server\90\DTS folder on your machine? What's in there?|||yeah there is indeed a C:\Program Files\Microsoft SQL Server\90\DTS\Binn directory with DTSPipeline.dll in there. But still re-registering the DLL did not help.|||What control container are you trying to add the dataflow task to?|||Control Flow. But as I mentioned in my earlier post I couldn't even get Import/Export wizard to work within Management Studio.

Help: error when trying to connect to server using sql server 2005 express

Dear all,

I have installed sql server 2005 Express with SQLADV.exe. But when I open Management Studio Express it has error:

Can not connect to server

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error locating Server/Instance Specified).

I searched on google but did not find any solution. Anyone know about that, please help me.

check this link http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

there are few things to be done to access database engine

(a) Remote connection to be enabled

(b) Create exception if firewall is enabled on this srever

(c) start SQL Browser service

(d) check the protocols

Madhu

|||

Dear Madhu K Nair,

I just use my local computer to test, how can I check for protocol and should I do it. Now I just use my computer to create a website and database in my computer too.

|||

check this http://support.microsoft.com/kb/914277

Madhu

|||

Please check my blog for this:

http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx