Property IsLocked is not available for Login '[sa]'

Hi everyone,
I'm at wits-end here and cannot figure out how in the world to show
the properties dialog window for the [sa] account (as well as one
other SQL account) in SQL Server 2005 Standard Edititon. This isn't
happening to all SQL accounts nor is it happening to any Windows
account on the server.
Below is the following error:
TITLE: Microsoft SQL Server Management Studio
--
Cannot show requested dialog.
--
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
--
Property IsLocked is not available for Login '[sa]'. This property may
not exist for this object, or may not be retrievable due to
insufficient access rights. (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?Prod...ed&LinkId=20476
--
BUTTONS:
OK
--
I've browsed the entire interweb (or at least the indexed pages of it
and every newsgroup I can think of) and have come to the conclusion
that this is one of the general errors that SQL 2005 kicks out when
things get good and screwed up.
Here are some futile attempts I've taken to remedy this error:
1) Executed this in a query windows: ALTER LOGIN sa WITH PASSWORD =
'DamnYouStupidError' UNLOCK
2) Verified all databases had a valid account as an owner (all have sa
as an owner)
3) Verfied the sa account did not have rights to an old or nonexistent
database.
4) Verifed SP2 is installed, which it is
5) Pulled out my hair
6) Cussed in public... a lot
7) Posted to the newsgroups
I've basically wasted the better part of two days trying everything
under the sun to fix this, but I feel that I've gotten no where. If
anyone has any suggestions shy of reinstalling SQL Server 2005, I'd
love to hear them.
Thanks,
JohnI have the same problem
Any solution about this?
Thanks
"John" <John.Eisbrener@.gmail.com> escribi en el mensaje
news:1177964038.783366.122620@.q75g2000hsh.googlegroups.com...
> Hi everyone,
> I'm at wits-end here and cannot figure out how in the world to show
> the properties dialog window for the [sa] account (as well as one
> other SQL account) in SQL Server 2005 Standard Edititon. This isn't
> happening to all SQL accounts nor is it happening to any Windows
> account on the server.
> Below is the following error:
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (SqlMgmt)
> --
> Property IsLocked is not available for Login '[sa]'. This property may
> not exist for this object, or may not be retrievable due to
> insufficient access rights. (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...ed&LinkId=20476
> --
> BUTTONS:
> OK
> --
> I've browsed the entire interweb (or at least the indexed pages of it
> and every newsgroup I can think of) and have come to the conclusion
> that this is one of the general errors that SQL 2005 kicks out when
> things get good and screwed up.
> Here are some futile attempts I've taken to remedy this error:
> 1) Executed this in a query windows: ALTER LOGIN sa WITH PASSWORD =
> 'DamnYouStupidError' UNLOCK
> 2) Verified all databases had a valid account as an owner (all have sa
> as an owner)
> 3) Verfied the sa account did not have rights to an old or nonexistent
> database.
> 4) Verifed SP2 is installed, which it is
> 5) Pulled out my hair
> 6) Cussed in public... a lot
> 7) Posted to the newsgroups
> I've basically wasted the better part of two days trying everything
> under the sun to fix this, but I feel that I've gotten no where. If
> anyone has any suggestions shy of reinstalling SQL Server 2005, I'd
> love to hear them.
> Thanks,
> John
>|||What operating system are you using? I'm having the same problem on Vista x6
4.
"John" wrote:

> Hi everyone,
> I'm at wits-end here and cannot figure out how in the world to show
> the properties dialog window for the [sa] account (as well as one
> other SQL account) in SQL Server 2005 Standard Edititon. This isn't
> happening to all SQL accounts nor is it happening to any Windows
> account on the server.
> Below is the following error:
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (SqlMgmt)
> --
> Property IsLocked is not available for Login '[sa]'. This property may
> not exist for this object, or may not be retrievable due to
> insufficient access rights. (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...ed&LinkId=20476
> --
> BUTTONS:
> OK
> --
> I've browsed the entire interweb (or at least the indexed pages of it
> and every newsgroup I can think of) and have come to the conclusion
> that this is one of the general errors that SQL 2005 kicks out when
> things get good and screwed up.
> Here are some futile attempts I've taken to remedy this error:
> 1) Executed this in a query windows: ALTER LOGIN sa WITH PASSWORD =
> 'DamnYouStupidError' UNLOCK
> 2) Verified all databases had a valid account as an owner (all have sa
> as an owner)
> 3) Verfied the sa account did not have rights to an old or nonexistent
> database.
> 4) Verifed SP2 is installed, which it is
> 5) Pulled out my hair
> 6) Cussed in public... a lot
> 7) Posted to the newsgroups
> I've basically wasted the better part of two days trying everything
> under the sun to fix this, but I feel that I've gotten no where. If
> anyone has any suggestions shy of reinstalling SQL Server 2005, I'd
> love to hear them.
> Thanks,
> John
>|||I have Windows 2003 R2 64bits. I use SQL Server 2005 x64 in cluster.
Others problem around this are
Activity Monitor doesn't work. ("Unable to launch activity monitor. You may
not have sufficient permissions")
"Akhiris" <Akhiris@.discussions.microsoft.com> escribi en el mensaje
news:23AB87FD-8F62-4913-9DF6-53E45BCEC20C@.microsoft.com...[vbcol=seagreen]
> What operating system are you using? I'm having the same problem on Vista
> x64.
> "John" wrote:
>|||The only "solution" that I've found is to backup all of your
databases, uninstall SQL and reinstall it. Then restore the databases
and finally as a preventative measure, switch ownership of each
database to the dbo account. I'm still of the believe that there is a
different way to remedy this situation, but I haven't found anything
yet.
John
On May 31, 9:03 am, "Jose Cebrian" <jcebrian2...@.yahoo.es> wrote:
> I have the same problem
> Any solution about this?
> Thanks
> "John" <John.Eisbre...@.gmail.com> escribi=F3 en el mensajenews:1177964038=
.783366.122620@.q75g2000hsh.googlegroups.com...[vbcol=seagreen]
>
>
>
>
>
=3D...[vbcol=seagreen]
>
>
>
>
>
>

properties owner and users owner.

In the properties section of a database there is a owner.
Under the users of a database there is the dbo with
a Login Name.
What is the difference between the 'two owners' ?
ben brugmanHi,
"Both can be same in most of the situation."
DBO - DBO is a role, A user with a DBO role can do any activities inside
the database.
Properties section of a database there is a owner?
He will be person who creates the database. By default he will a DBO. This
owner can be changed using the procedure,
sp_changedbowner.
Thanks
Hari
MCDBA
"ben brugman" <ben@.niethier.nl> wrote in message
news:eCuSx$chEHA.4064@.TK2MSFTNGP12.phx.gbl...
> In the properties section of a database there is a owner.
> Under the users of a database there is the dbo with
> a Login Name.
> What is the difference between the 'two owners' ?
> ben brugman
>|||Thanks for your time and quick response,
see inline :
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O2$NJIdhEHA.2052@.tk2msftngp13.phx.gbl...
> Hi,
> "Both can be same in most of the situation."
I tried to make both the same.

> DBO - DBO is a role, A user with a DBO role can do any activities inside
> the database.
> Properties section of a database there is a owner?
> He will be person who creates the database. By default he will a DBO. This
> owner can be changed using the procedure,
> sp_changedbowner.
I cannot change the owner because :
"
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46
The proposed new database owner is already a user in the database.
"
And in the database he is the dbo owner, so I do not think deleting that
owner
is wise.
I still have difficulty grasping the difference between the two owners and
still would like them to be the same.
Thanks
ben brugman

> Thanks
> Hari
> MCDBA
>
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eCuSx$chEHA.4064@.TK2MSFTNGP12.phx.gbl...
>|||Hari
DBO is a privileged user.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O2$NJIdhEHA.2052@.tk2msftngp13.phx.gbl...
> Hi,
> "Both can be same in most of the situation."
> DBO - DBO is a role, A user with a DBO role can do any activities inside
> the database.
> Properties section of a database there is a owner?
> He will be person who creates the database. By default he will a DBO. This
> owner can be changed using the procedure,
> sp_changedbowner.
> Thanks
> Hari
> MCDBA
>
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eCuSx$chEHA.4064@.TK2MSFTNGP12.phx.gbl...
>|||'dbo' is a special database user and must exist in every database. The
database owner is the *login* that is mapped to the database 'dbo' user and
this mapping is stored in 2 places, sysdatabases and sysusers. These should
normally be the same login but can get out-of-sync in some situations, such
as a restore or attach. The query below will return the same login if the
owner entries are synchronized:
USE MyDatabase
SELECT 'sysdatabases mapping=' + SUSER_SNAME(sid)
FROM master..sysdatabases
WHERE name = DB_NAME()
UNION ALL
SELECT 'sysusers mapping=' + SUSER_SNAME(sid)
FROM sysusers
WHERE name = 'dbo'
You can execute sp_changedbowner to change or correct the mapping. If you
get error 15110, ensure the specified login is not already a database user
since a login can be mapped to only one database user at a time.
The 15110 error can also be raised due to an out-of-sync condition mentioned
above. In that case, temporarily change database ownership to a
non-conflicting login and then to the desired login like the example below:
USE MyDatabase
EXEC sp_addlogin 'TempOwner'
EXEC sp_changedbowner 'TempOwner'
EXEC sp_changedbowner 'MyDatabaseOwner'
EXEC sp_droplogin 'TempOwner'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:%23nqiINdhEHA.3076@.tk2msftngp13.phx.gbl...
> Thanks for your time and quick response,
> see inline :
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O2$NJIdhEHA.2052@.tk2msftngp13.phx.gbl...
> I tried to make both the same.
>
inside[vbcol=seagreen]
This[vbcol=seagreen]
> I cannot change the owner because :
> "
> Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46
> The proposed new database owner is already a user in the database.
> "
> And in the database he is the dbo owner, so I do not think deleting that
> owner
> is wise.
> I still have difficulty grasping the difference between the two owners and
> still would like them to be the same.
> Thanks
> ben brugman
>
>sql

properties owner and users owner.

In the properties section of a database there is a owner.
Under the users of a database there is the dbo with
a Login Name.
What is the difference between the 'two owners' ?
ben brugmanHi,
"Both can be same in most of the situation."
DBO - DBO is a role, A user with a DBO role can do any activities inside
the database.
Properties section of a database there is a owner?
He will be person who creates the database. By default he will a DBO. This
owner can be changed using the procedure,
sp_changedbowner.
Thanks
Hari
MCDBA
"ben brugman" <ben@.niethier.nl> wrote in message
news:eCuSx$chEHA.4064@.TK2MSFTNGP12.phx.gbl...
> In the properties section of a database there is a owner.
> Under the users of a database there is the dbo with
> a Login Name.
> What is the difference between the 'two owners' ?
> ben brugman
>|||Hari
DBO is a privileged user.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O2$NJIdhEHA.2052@.tk2msftngp13.phx.gbl...
> Hi,
> "Both can be same in most of the situation."
> DBO - DBO is a role, A user with a DBO role can do any activities inside
> the database.
> Properties section of a database there is a owner?
> He will be person who creates the database. By default he will a DBO. This
> owner can be changed using the procedure,
> sp_changedbowner.
> Thanks
> Hari
> MCDBA
>
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eCuSx$chEHA.4064@.TK2MSFTNGP12.phx.gbl...
> > In the properties section of a database there is a owner.
> >
> > Under the users of a database there is the dbo with
> > a Login Name.
> >
> > What is the difference between the 'two owners' ?
> >
> > ben brugman
> >
> >
>|||Thanks for your time and quick response,
see inline :
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O2$NJIdhEHA.2052@.tk2msftngp13.phx.gbl...
> Hi,
> "Both can be same in most of the situation."
I tried to make both the same.
> DBO - DBO is a role, A user with a DBO role can do any activities inside
> the database.
> Properties section of a database there is a owner?
> He will be person who creates the database. By default he will a DBO. This
> owner can be changed using the procedure,
> sp_changedbowner.
I cannot change the owner because :
"
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46
The proposed new database owner is already a user in the database.
"
And in the database he is the dbo owner, so I do not think deleting that
owner
is wise.
I still have difficulty grasping the difference between the two owners and
still would like them to be the same.
Thanks
ben brugman
> Thanks
> Hari
> MCDBA
>
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eCuSx$chEHA.4064@.TK2MSFTNGP12.phx.gbl...
> > In the properties section of a database there is a owner.
> >
> > Under the users of a database there is the dbo with
> > a Login Name.
> >
> > What is the difference between the 'two owners' ?
> >
> > ben brugman
> >
> >
>|||'dbo' is a special database user and must exist in every database. The
database owner is the *login* that is mapped to the database 'dbo' user and
this mapping is stored in 2 places, sysdatabases and sysusers. These should
normally be the same login but can get out-of-sync in some situations, such
as a restore or attach. The query below will return the same login if the
owner entries are synchronized:
USE MyDatabase
SELECT 'sysdatabases mapping=' + SUSER_SNAME(sid)
FROM master..sysdatabases
WHERE name = DB_NAME()
UNION ALL
SELECT 'sysusers mapping=' + SUSER_SNAME(sid)
FROM sysusers
WHERE name = 'dbo'
You can execute sp_changedbowner to change or correct the mapping. If you
get error 15110, ensure the specified login is not already a database user
since a login can be mapped to only one database user at a time.
The 15110 error can also be raised due to an out-of-sync condition mentioned
above. In that case, temporarily change database ownership to a
non-conflicting login and then to the desired login like the example below:
USE MyDatabase
EXEC sp_addlogin 'TempOwner'
EXEC sp_changedbowner 'TempOwner'
EXEC sp_changedbowner 'MyDatabaseOwner'
EXEC sp_droplogin 'TempOwner'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:%23nqiINdhEHA.3076@.tk2msftngp13.phx.gbl...
> Thanks for your time and quick response,
> see inline :
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O2$NJIdhEHA.2052@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > "Both can be same in most of the situation."
> I tried to make both the same.
> >
> > DBO - DBO is a role, A user with a DBO role can do any activities
inside
> > the database.
> >
> > Properties section of a database there is a owner?
> >
> > He will be person who creates the database. By default he will a DBO.
This
> > owner can be changed using the procedure,
> >
> > sp_changedbowner.
> I cannot change the owner because :
> "
> Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46
> The proposed new database owner is already a user in the database.
> "
> And in the database he is the dbo owner, so I do not think deleting that
> owner
> is wise.
> I still have difficulty grasping the difference between the two owners and
> still would like them to be the same.
> Thanks
> ben brugman
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> > "ben brugman" <ben@.niethier.nl> wrote in message
> > news:eCuSx$chEHA.4064@.TK2MSFTNGP12.phx.gbl...
> > > In the properties section of a database there is a owner.
> > >
> > > Under the users of a database there is the dbo with
> > > a Login Name.
> > >
> > > What is the difference between the 'two owners' ?
> > >
> > > ben brugman
> > >
> > >
> >
> >
>

properties owner and users owner.

In the properties section of a database there is a owner.
Under the users of a database there is the dbo with
a Login Name.
What is the difference between the 'two owners' ?
ben brugman
Hi,
"Both can be same in most of the situation."
DBO - DBO is a role, A user with a DBO role can do any activities inside
the database.
Properties section of a database there is a owner?
He will be person who creates the database. By default he will a DBO. This
owner can be changed using the procedure,
sp_changedbowner.
Thanks
Hari
MCDBA
"ben brugman" <ben@.niethier.nl> wrote in message
news:eCuSx$chEHA.4064@.TK2MSFTNGP12.phx.gbl...
> In the properties section of a database there is a owner.
> Under the users of a database there is the dbo with
> a Login Name.
> What is the difference between the 'two owners' ?
> ben brugman
>
|||Thanks for your time and quick response,
see inline :
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O2$NJIdhEHA.2052@.tk2msftngp13.phx.gbl...
> Hi,
> "Both can be same in most of the situation."
I tried to make both the same.

> DBO - DBO is a role, A user with a DBO role can do any activities inside
> the database.
> Properties section of a database there is a owner?
> He will be person who creates the database. By default he will a DBO. This
> owner can be changed using the procedure,
> sp_changedbowner.
I cannot change the owner because :
"
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46
The proposed new database owner is already a user in the database.
"
And in the database he is the dbo owner, so I do not think deleting that
owner
is wise.
I still have difficulty grasping the difference between the two owners and
still would like them to be the same.
Thanks
ben brugman

> Thanks
> Hari
> MCDBA
>
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eCuSx$chEHA.4064@.TK2MSFTNGP12.phx.gbl...
>
|||Hari
DBO is a privileged user.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O2$NJIdhEHA.2052@.tk2msftngp13.phx.gbl...
> Hi,
> "Both can be same in most of the situation."
> DBO - DBO is a role, A user with a DBO role can do any activities inside
> the database.
> Properties section of a database there is a owner?
> He will be person who creates the database. By default he will a DBO. This
> owner can be changed using the procedure,
> sp_changedbowner.
> Thanks
> Hari
> MCDBA
>
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eCuSx$chEHA.4064@.TK2MSFTNGP12.phx.gbl...
>
|||'dbo' is a special database user and must exist in every database. The
database owner is the *login* that is mapped to the database 'dbo' user and
this mapping is stored in 2 places, sysdatabases and sysusers. These should
normally be the same login but can get out-of-sync in some situations, such
as a restore or attach. The query below will return the same login if the
owner entries are synchronized:
USE MyDatabase
SELECT 'sysdatabases mapping=' + SUSER_SNAME(sid)
FROM master..sysdatabases
WHERE name = DB_NAME()
UNION ALL
SELECT 'sysusers mapping=' + SUSER_SNAME(sid)
FROM sysusers
WHERE name = 'dbo'
You can execute sp_changedbowner to change or correct the mapping. If you
get error 15110, ensure the specified login is not already a database user
since a login can be mapped to only one database user at a time.
The 15110 error can also be raised due to an out-of-sync condition mentioned
above. In that case, temporarily change database ownership to a
non-conflicting login and then to the desired login like the example below:
USE MyDatabase
EXEC sp_addlogin 'TempOwner'
EXEC sp_changedbowner 'TempOwner'
EXEC sp_changedbowner 'MyDatabaseOwner'
EXEC sp_droplogin 'TempOwner'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:%23nqiINdhEHA.3076@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks for your time and quick response,
> see inline :
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:O2$NJIdhEHA.2052@.tk2msftngp13.phx.gbl...
> I tried to make both the same.
inside[vbcol=seagreen]
This
> I cannot change the owner because :
> "
> Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46
> The proposed new database owner is already a user in the database.
> "
> And in the database he is the dbo owner, so I do not think deleting that
> owner
> is wise.
> I still have difficulty grasping the difference between the two owners and
> still would like them to be the same.
> Thanks
> ben brugman
>
>

Prompt for login credentials using Windows-NT Authentication

Hello,
I am using a SQL Server (MSDE) db, currently configured to only accept
Windows-NT Authentication logins. This has worked well for a while -
much easier to administer. However I have a group of users that have
presented a special problem.
These users have valid logins under a valid Windows Domain ie
<Domain>\<user>. Unlike most users, however, they do not login to the
windows domain when they log into their machines (instead they login
to the local machine). In cases other than mine, when they access
applications/web services that require authentication to the said
windows domain, they are prompted for login credentials (ie
username=<domain>\<user> and password). They can then proceed to use
the application/web service.
With my SQL Server database, however, this does not happen. When they
attempt to login to my database, I am guessing that the server figures
out that they are not currently logged in to the windows domain, so
denies them access outright. They are given no opportunity to enter
their credentials. They are attempting to access the database using
data access pages / OWC10.
Is there any way that I can setup a connection string which allows me
to continue using Windows-NT Authentication, but will prompt users for
login credentials if they are not currently logged in to the required
Windows Domain?
Thanks heaps for your help.
Nick TompsonIt's been a while since I've worked with data access pages, but as I
recollect the connection is hard-wired into the page. What I think
you're going to have to do is to provide a login where they can join
the domain that is accredited with your SQL Server prior to loading
the page. I could be wrong about this -- you might want to post the
question in the microsoft.public.access.dataaccess.pages ng.
-- Mary
On 2 May 2004 23:48:13 -0700, ntompson@.utas.edu.au (Nick Tompson)
wrote:

>Hello,
>I am using a SQL Server (MSDE) db, currently configured to only accept
>Windows-NT Authentication logins. This has worked well for a while -
>much easier to administer. However I have a group of users that have
>presented a special problem.
>These users have valid logins under a valid Windows Domain ie
><Domain>\<user>. Unlike most users, however, they do not login to the
>windows domain when they log into their machines (instead they login
>to the local machine). In cases other than mine, when they access
>applications/web services that require authentication to the said
>windows domain, they are prompted for login credentials (ie
>username=<domain>\<user> and password). They can then proceed to use
>the application/web service.
>With my SQL Server database, however, this does not happen. When they
>attempt to login to my database, I am guessing that the server figures
>out that they are not currently logged in to the windows domain, so
>denies them access outright. They are given no opportunity to enter
>their credentials. They are attempting to access the database using
>data access pages / OWC10.
>Is there any way that I can setup a connection string which allows me
>to continue using Windows-NT Authentication, but will prompt users for
>login credentials if they are not currently logged in to the required
>Windows Domain?
>Thanks heaps for your help.
>Nick Tompson|||If the users log on to the local machine then that is the login tha SQL
Server expects. You can workaround this problem by creating a user account
on the SQL Server machine with the same name and password as the users who
are logging in and grant that login a login within SQL Server. This should
allow them to log in to SQL Server using the local machine account.
Outside of that I am not sure there is a way, unless you can get the IIS
anonymous login to be used by these users.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Thankyou both for your comments. I think that your solutions may work,
however, I am keen to stick with Windows-NT Authentication if I can, as
I do not want to have to manage usernames and passwords.
Any other ideas?
Thanks
Nick Tompson
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||If you don't want to create mirrored local accounts, you might try having
the users map a drive to a share on the SQL Server using their domain
account. I believe SQL Server will use these security credentials for the
trusted connection.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nick Tompson" <ntompson@.utas.edu.au> wrote in message
news:uOTSDsWMEHA.3012@.tk2msftngp13.phx.gbl...
> Thankyou both for your comments. I think that your solutions may work,
> however, I am keen to stick with Windows-NT Authentication if I can, as
> I do not want to have to manage usernames and passwords.
> Any other ideas?
> Thanks
> Nick Tompson
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Prompt for login credentials using Windows-NT Authentication

Hello,
I am using a SQL Server (MSDE) db, currently configured to only accept
Windows-NT Authentication logins. This has worked well for a while -
much easier to administer. However I have a group of users that have
presented a special problem.
These users have valid logins under a valid Windows Domain ie
<Domain>\<user>. Unlike most users, however, they do not login to the
windows domain when they log into their machines (instead they login
to the local machine). In cases other than mine, when they access
applications/web services that require authentication to the said
windows domain, they are prompted for login credentials (ie
username=<domain>\<user> and password). They can then proceed to use
the application/web service.
With my SQL Server database, however, this does not happen. When they
attempt to login to my database, I am guessing that the server figures
out that they are not currently logged in to the windows domain, so
denies them access outright. They are given no opportunity to enter
their credentials. They are attempting to access the database using
data access pages / OWC10.
Is there any way that I can setup a connection string which allows me
to continue using Windows-NT Authentication, but will prompt users for
login credentials if they are not currently logged in to the required
Windows Domain?
Thanks heaps for your help.
Nick TompsonIt's been a while since I've worked with data access pages, but as I
recollect the connection is hard-wired into the page. What I think
you're going to have to do is to provide a login where they can join
the domain that is accredited with your SQL Server prior to loading
the page. I could be wrong about this -- you might want to post the
question in the microsoft.public.access.dataaccess.pages ng.
-- Mary
On 2 May 2004 23:48:13 -0700, ntompson@.utas.edu.au (Nick Tompson)
wrote:
>Hello,
>I am using a SQL Server (MSDE) db, currently configured to only accept
>Windows-NT Authentication logins. This has worked well for a while -
>much easier to administer. However I have a group of users that have
>presented a special problem.
>These users have valid logins under a valid Windows Domain ie
><Domain>\<user>. Unlike most users, however, they do not login to the
>windows domain when they log into their machines (instead they login
>to the local machine). In cases other than mine, when they access
>applications/web services that require authentication to the said
>windows domain, they are prompted for login credentials (ie
>username=<domain>\<user> and password). They can then proceed to use
>the application/web service.
>With my SQL Server database, however, this does not happen. When they
>attempt to login to my database, I am guessing that the server figures
>out that they are not currently logged in to the windows domain, so
>denies them access outright. They are given no opportunity to enter
>their credentials. They are attempting to access the database using
>data access pages / OWC10.
>Is there any way that I can setup a connection string which allows me
>to continue using Windows-NT Authentication, but will prompt users for
>login credentials if they are not currently logged in to the required
>Windows Domain?
>Thanks heaps for your help.
>Nick Tompson|||If the users log on to the local machine then that is the login tha SQL
Server expects. You can workaround this problem by creating a user account
on the SQL Server machine with the same name and password as the users who
are logging in and grant that login a login within SQL Server. This should
allow them to log in to SQL Server using the local machine account.
Outside of that I am not sure there is a way, unless you can get the IIS
anonymous login to be used by these users.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Prompt for login credentials using Windows-NT Authentication

Hello,
I am using a SQL Server (MSDE) db, currently configured to only accept
Windows-NT Authentication logins. This has worked well for a while -
much easier to administer. However I have a group of users that have
presented a special problem.
These users have valid logins under a valid Windows Domain ie
<Domain>\<user>. Unlike most users, however, they do not login to the
windows domain when they log into their machines (instead they login
to the local machine). In cases other than mine, when they access
applications/web services that require authentication to the said
windows domain, they are prompted for login credentials (ie
username=<domain>\<user> and password). They can then proceed to use
the application/web service.
With my SQL Server database, however, this does not happen. When they
attempt to login to my database, I am guessing that the server figures
out that they are not currently logged in to the windows domain, so
denies them access outright. They are given no opportunity to enter
their credentials. They are attempting to access the database using
data access pages / OWC10.
Is there any way that I can setup a connection string which allows me
to continue using Windows-NT Authentication, but will prompt users for
login credentials if they are not currently logged in to the required
Windows Domain?
Thanks heaps for your help.
Nick Tompson
It's been a while since I've worked with data access pages, but as I
recollect the connection is hard-wired into the page. What I think
you're going to have to do is to provide a login where they can join
the domain that is accredited with your SQL Server prior to loading
the page. I could be wrong about this -- you might want to post the
question in the microsoft.public.access.dataaccess.pages ng.
-- Mary
On 2 May 2004 23:48:13 -0700, ntompson@.utas.edu.au (Nick Tompson)
wrote:

>Hello,
>I am using a SQL Server (MSDE) db, currently configured to only accept
>Windows-NT Authentication logins. This has worked well for a while -
>much easier to administer. However I have a group of users that have
>presented a special problem.
>These users have valid logins under a valid Windows Domain ie
><Domain>\<user>. Unlike most users, however, they do not login to the
>windows domain when they log into their machines (instead they login
>to the local machine). In cases other than mine, when they access
>applications/web services that require authentication to the said
>windows domain, they are prompted for login credentials (ie
>username=<domain>\<user> and password). They can then proceed to use
>the application/web service.
>With my SQL Server database, however, this does not happen. When they
>attempt to login to my database, I am guessing that the server figures
>out that they are not currently logged in to the windows domain, so
>denies them access outright. They are given no opportunity to enter
>their credentials. They are attempting to access the database using
>data access pages / OWC10.
>Is there any way that I can setup a connection string which allows me
>to continue using Windows-NT Authentication, but will prompt users for
>login credentials if they are not currently logged in to the required
>Windows Domain?
>Thanks heaps for your help.
>Nick Tompson
|||If the users log on to the local machine then that is the login tha SQL
Server expects. You can workaround this problem by creating a user account
on the SQL Server machine with the same name and password as the users who
are logging in and grant that login a login within SQL Server. This should
allow them to log in to SQL Server using the local machine account.
Outside of that I am not sure there is a way, unless you can get the IIS
anonymous login to be used by these users.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Thankyou both for your comments. I think that your solutions may work,
however, I am keen to stick with Windows-NT Authentication if I can, as
I do not want to have to manage usernames and passwords.
Any other ideas?
Thanks
Nick Tompson
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||If you don't want to create mirrored local accounts, you might try having
the users map a drive to a share on the SQL Server using their domain
account. I believe SQL Server will use these security credentials for the
trusted connection.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nick Tompson" <ntompson@.utas.edu.au> wrote in message
news:uOTSDsWMEHA.3012@.tk2msftngp13.phx.gbl...
> Thankyou both for your comments. I think that your solutions may work,
> however, I am keen to stick with Windows-NT Authentication if I can, as
> I do not want to have to manage usernames and passwords.
> Any other ideas?
> Thanks
> Nick Tompson
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
sql

prolem with "create login from asymmetric key"

Greetings...

I'm trying to create assembly with "PERMISSION_SET = UNSAFE".
For that I've signed assembly's .dll and installed root certificate to “Trusted Root Certificate Authority.”: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx
now I'm trying to create login from asymmetric key:

USE master
GO

CREATE ASYMMETRIC KEY SQLCLRTestKey
FROM EXECUTABLE FILE = 'C:\Documents and Settings\All Users\Documents\hunter\StoredProcedures.dll'
CREATE LOGIN SQLCLRTestLogin
FROM ASYMMETRIC KEY SQLCLRTestKey

but I'm receiving error: "Cannot find the asymmetric key 'SQLCLRTestKey', because it does not exist or you do not have permission."

What's wrong?

Best regards...

I recommend you take a look at a sample like the AdventureWorks CLR sample which demostrates all the steps necessary in signing, registering the asymmetric key, registering the login, and finally registering the unsafe assembly. You can find the Yukon SP2 samples here: http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en. Once you've installed the code samples, by default you will find the AdventureWorks CLR sample here: SystemDrive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\AdventureWorks. The readme file for the sample will walk you through each step of the process. You don't need to install any root certificates to use asymmetic keys.

--Bonnie [MSFT]

|||? I'm not a big fan of those instructions. A much, much easier method: First, read this: http://www.sommarskog.se/grantperm.html#certandbulkcopy Follow the instructions therein for setting up a certificate, creating a login using the certificate, etc. But instead of granting bulk copy permissions to the login, grant EXTERNAL ACCESS ASSEMBLY permissions. Backup the certificate and restore it into the database in which you want to create the assembly. Create and catalog the assembly -- no strong name needed (unless you want to use one). Sign your assembly with the same certificate you created in the master DB, and you're good to go. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- ""_hunter"@.discussions..microsoft.com" <"=?UTF-8?B?X2h1bnRlcg==?="@.discussions.microsoft.com> wrote in message news:e74340fa-4ba8-406e-94f4-51698ba38574@.discussions.microsoft.com...Greetings...I'm trying to create assembly with "PERMISSION_SET = UNSAFE".For that I've signed assembly's .dll and installed root certificate to “Trusted Root Certificate Authority.”: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies..aspxnow I'm trying to create login from asymmetric key:USE master GO CREATE ASYMMETRIC KEY SQLCLRTestKeyFROM EXECUTABLE FILE = 'C:\Documents and Settings\All Users\Documents\hunter\StoredProcedures.dll'CREATE LOGIN SQLCLRTestLoginFROM ASYMMETRIC KEY SQLCLRTestKeybut I'm receiving error: "Cannot find the asymmetric key 'SQLCLRTestKey', because it does not exist or you do not have permission."What's wrong?Best regards...|||

BonnieFe, can You be more specific? Ideally just piont on my mistakes...

I've created key by "sn -k SampleKey.snk"

Then in my project's properties i've selected it in Signing->Sign the assembly->Choose a strong name &key file:-><Browse...>

Build->Rebuild Solution

Then in "SQL Server Management Studio":

EXEC('CREATE ASYMMETRIC KEY UnsafeSample_Key FROM EXECUTABLE FILE = ''C:\\StoredProcedures.dll'';');

CREATE LOGIN UnsafeSample_Login FROM ASYMMETRIC KEY UnsafeSample_Key
and I'm reciving the same error: "Cannot find the asymmetric key 'UnsafeSample_Key', because it does not exist or you do not have permission."

|||

NNTP User, I'm trying such:

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'

GO

BACKUP CERTIFICATE UnsafeSample_Certificate TO FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.pvk' ,

ENCRYPTION BY PASSWORD = 'Tomorrow never knows',

DECRYPTION BY PASSWORD = 'All you need is love')

GO

CREATE USER UnsafeSample_Login

FROM CERTIFICATE UnsafeSample_Certificate

GO

GRANT EXTERNAL ACCESS ASSEMBLY

TO UnsafeSample_Login

GO

code, but I'm reciving an error "Cannot find the login 'UnsafeSample_Login', because it does not exist or you do not have permission." on GRANT-command.

|||? Look at your code again -- you've created a USER, rather than a LOGIN -- you need to create the login first, then create a user based on the login, in your database (but not in master). -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- ""_hunter"@.discussions..microsoft.com" <"=?UTF-8?B?X2h1bnRlcg==?="@.discussions.microsoft.com> wrote in message news:46c433b9-51c8-4343-95b2-925ce937dbf4_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com NNTP User, I'm trying such: 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' GO BACKUP CERTIFICATE UnsafeSample_Certificate TO FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.cer' WITH PRIVATE KEY (FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.pvk' , ENCRYPTION BY PASSWORD = 'Tomorrow never knows', DECRYPTION BY PASSWORD = 'All you need is love') GO CREATE USER UnsafeSample_Login FROM CERTIFICATE UnsafeSample_Certificate GO GRANT EXTERNAL ACCESS ASSEMBLY TO UnsafeSample_Login GO code, but I'm reciving an error "Cannot find the login 'UnsafeSample_Login', because it does not exist or you do not have permission." on GRANT-command.

prolem with "create login from asymmetric key"

Greetings...

I'm trying to create assembly with "PERMISSION_SET = UNSAFE".
For that I've signed assembly's .dll and installed root certificate to “Trusted Root Certificate Authority.”: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx
now I'm trying to create login from asymmetric key:

USE master
GO

CREATE ASYMMETRIC KEY SQLCLRTestKey
FROM EXECUTABLE FILE = 'C:\Documents and Settings\All Users\Documents\hunter\StoredProcedures.dll'
CREATE LOGIN SQLCLRTestLogin
FROM ASYMMETRIC KEY SQLCLRTestKey

but I'm receiving error: "Cannot find the asymmetric key 'SQLCLRTestKey', because it does not exist or you do not have permission."

What's wrong?

Best regards...

I recommend you take a look at a sample like the AdventureWorks CLR sample which demostrates all the steps necessary in signing, registering the asymmetric key, registering the login, and finally registering the unsafe assembly. You can find the Yukon SP2 samples here: http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en. Once you've installed the code samples, by default you will find the AdventureWorks CLR sample here: SystemDrive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\AdventureWorks. The readme file for the sample will walk you through each step of the process. You don't need to install any root certificates to use asymmetic keys.

--Bonnie [MSFT]

|||? I'm not a big fan of those instructions. A much, much easier method: First, read this: http://www.sommarskog.se/grantperm.html#certandbulkcopy Follow the instructions therein for setting up a certificate, creating a login using the certificate, etc. But instead of granting bulk copy permissions to the login, grant EXTERNAL ACCESS ASSEMBLY permissions. Backup the certificate and restore it into the database in which you want to create the assembly. Create and catalog the assembly -- no strong name needed (unless you want to use one). Sign your assembly with the same certificate you created in the master DB, and you're good to go. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- ""_hunter"@.discussions..microsoft.com" <"=?UTF-8?B?X2h1bnRlcg==?="@.discussions.microsoft.com> wrote in message news:e74340fa-4ba8-406e-94f4-51698ba38574@.discussions.microsoft.com...Greetings...I'm trying to create assembly with "PERMISSION_SET = UNSAFE".For that I've signed assembly's .dll and installed root certificate to “Trusted Root Certificate Authority.”: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies..aspxnow I'm trying to create login from asymmetric key:USE master GO CREATE ASYMMETRIC KEY SQLCLRTestKeyFROM EXECUTABLE FILE = 'C:\Documents and Settings\All Users\Documents\hunter\StoredProcedures.dll'CREATE LOGIN SQLCLRTestLoginFROM ASYMMETRIC KEY SQLCLRTestKeybut I'm receiving error: "Cannot find the asymmetric key 'SQLCLRTestKey', because it does not exist or you do not have permission."What's wrong?Best regards...|||

BonnieFe, can You be more specific? Ideally just piont on my mistakes...

I've created key by "sn -k SampleKey.snk"

Then in my project's properties i've selected it in Signing->Sign the assembly->Choose a strong name &key file:-><Browse...>

Build->Rebuild Solution

Then in "SQL Server Management Studio":

EXEC('CREATE ASYMMETRIC KEY UnsafeSample_Key FROM EXECUTABLE FILE = ''C:\\StoredProcedures.dll'';');

CREATE LOGIN UnsafeSample_Login FROM ASYMMETRIC KEY UnsafeSample_Key
and I'm reciving the same error: "Cannot find the asymmetric key 'UnsafeSample_Key', because it does not exist or you do not have permission."

|||

NNTP User, I'm trying such:

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'

GO

BACKUP CERTIFICATE UnsafeSample_Certificate TO FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.pvk' ,

ENCRYPTION BY PASSWORD = 'Tomorrow never knows',

DECRYPTION BY PASSWORD = 'All you need is love')

GO

CREATE USER UnsafeSample_Login

FROM CERTIFICATE UnsafeSample_Certificate

GO

GRANT EXTERNAL ACCESS ASSEMBLY

TO UnsafeSample_Login

GO

code, but I'm reciving an error "Cannot find the login 'UnsafeSample_Login', because it does not exist or you do not have permission." on GRANT-command.

|||? Look at your code again -- you've created a USER, rather than a LOGIN -- you need to create the login first, then create a user based on the login, in your database (but not in master). -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- ""_hunter"@.discussions..microsoft.com" <"=?UTF-8?B?X2h1bnRlcg==?="@.discussions.microsoft.com> wrote in message news:46c433b9-51c8-4343-95b2-925ce937dbf4_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com NNTP User, I'm trying such: 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' GO BACKUP CERTIFICATE UnsafeSample_Certificate TO FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.cer' WITH PRIVATE KEY (FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.pvk' , ENCRYPTION BY PASSWORD = 'Tomorrow never knows', DECRYPTION BY PASSWORD = 'All you need is love') GO CREATE USER UnsafeSample_Login FROM CERTIFICATE UnsafeSample_Certificate GO GRANT EXTERNAL ACCESS ASSEMBLY TO UnsafeSample_Login GO code, but I'm reciving an error "Cannot find the login 'UnsafeSample_Login', because it does not exist or you do not have permission." on GRANT-command.

prolem with &quot;create login from asymmetric key&quot;

Greetings...

I'm trying to create assembly with "PERMISSION_SET = UNSAFE".
For that I've signed assembly's .dll and installed root certificate to “Trusted Root Certificate Authority.”: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx
now I'm trying to create login from asymmetric key:

USE master
GO

CREATE ASYMMETRIC KEY SQLCLRTestKey
FROM EXECUTABLE FILE = 'C:\Documents and Settings\All Users\Documents\hunter\StoredProcedures.dll'
CREATE LOGIN SQLCLRTestLogin
FROM ASYMMETRIC KEY SQLCLRTestKey

but I'm receiving error: "Cannot find the asymmetric key 'SQLCLRTestKey', because it does not exist or you do not have permission."

What's wrong?

Best regards...

I recommend you take a look at a sample like the AdventureWorks CLR sample which demostrates all the steps necessary in signing, registering the asymmetric key, registering the login, and finally registering the unsafe assembly. You can find the Yukon SP2 samples here: http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en. Once you've installed the code samples, by default you will find the AdventureWorks CLR sample here: SystemDrive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\AdventureWorks. The readme file for the sample will walk you through each step of the process. You don't need to install any root certificates to use asymmetic keys.

--Bonnie [MSFT]

|||? I'm not a big fan of those instructions. A much, much easier method: First, read this: http://www.sommarskog.se/grantperm.html#certandbulkcopy Follow the instructions therein for setting up a certificate, creating a login using the certificate, etc. But instead of granting bulk copy permissions to the login, grant EXTERNAL ACCESS ASSEMBLY permissions. Backup the certificate and restore it into the database in which you want to create the assembly. Create and catalog the assembly -- no strong name needed (unless you want to use one). Sign your assembly with the same certificate you created in the master DB, and you're good to go. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- ""_hunter"@.discussions..microsoft.com" <"=?UTF-8?B?X2h1bnRlcg==?="@.discussions.microsoft.com> wrote in message news:e74340fa-4ba8-406e-94f4-51698ba38574@.discussions.microsoft.com...Greetings...I'm trying to create assembly with "PERMISSION_SET = UNSAFE".For that I've signed assembly's .dll and installed root certificate to “Trusted Root Certificate Authority.”: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies..aspxnow I'm trying to create login from asymmetric key:USE master GO CREATE ASYMMETRIC KEY SQLCLRTestKeyFROM EXECUTABLE FILE = 'C:\Documents and Settings\All Users\Documents\hunter\StoredProcedures.dll'CREATE LOGIN SQLCLRTestLoginFROM ASYMMETRIC KEY SQLCLRTestKeybut I'm receiving error: "Cannot find the asymmetric key 'SQLCLRTestKey', because it does not exist or you do not have permission."What's wrong?Best regards...|||

BonnieFe, can You be more specific? Ideally just piont on my mistakes...

I've created key by "sn -k SampleKey.snk"

Then in my project's properties i've selected it in Signing->Sign the assembly->Choose a strong name &key file:-><Browse...>

Build->Rebuild Solution

Then in "SQL Server Management Studio":

EXEC('CREATE ASYMMETRIC KEY UnsafeSample_Key FROM EXECUTABLE FILE = ''C:\\StoredProcedures.dll'';');

CREATE LOGIN UnsafeSample_Login FROM ASYMMETRIC KEY UnsafeSample_Key
and I'm reciving the same error: "Cannot find the asymmetric key 'UnsafeSample_Key', because it does not exist or you do not have permission."

|||

NNTP User, I'm trying such:

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'

GO

BACKUP CERTIFICATE UnsafeSample_Certificate TO FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.pvk' ,

ENCRYPTION BY PASSWORD = 'Tomorrow never knows',

DECRYPTION BY PASSWORD = 'All you need is love')

GO

CREATE USER UnsafeSample_Login

FROM CERTIFICATE UnsafeSample_Certificate

GO

GRANT EXTERNAL ACCESS ASSEMBLY

TO UnsafeSample_Login

GO

code, but I'm reciving an error "Cannot find the login 'UnsafeSample_Login', because it does not exist or you do not have permission." on GRANT-command.

|||? Look at your code again -- you've created a USER, rather than a LOGIN -- you need to create the login first, then create a user based on the login, in your database (but not in master). -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- ""_hunter"@.discussions..microsoft.com" <"=?UTF-8?B?X2h1bnRlcg==?="@.discussions.microsoft.com> wrote in message news:46c433b9-51c8-4343-95b2-925ce937dbf4_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com NNTP User, I'm trying such: 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' GO BACKUP CERTIFICATE UnsafeSample_Certificate TO FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.cer' WITH PRIVATE KEY (FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.pvk' , ENCRYPTION BY PASSWORD = 'Tomorrow never knows', DECRYPTION BY PASSWORD = 'All you need is love') GO CREATE USER UnsafeSample_Login FROM CERTIFICATE UnsafeSample_Certificate GO GRANT EXTERNAL ACCESS ASSEMBLY TO UnsafeSample_Login GO code, but I'm reciving an error "Cannot find the login 'UnsafeSample_Login', because it does not exist or you do not have permission." on GRANT-command.

prolem with &amp;quot;create login from asymmetric key&amp;quot;

Greetings...

I'm trying to create assembly with "PERMISSION_SET = UNSAFE".
For that I've signed assembly's .dll and installed root certificate to “Trusted Root Certificate Authority.”: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx
now I'm trying to create login from asymmetric key:

USE master
GO

CREATE ASYMMETRIC KEY SQLCLRTestKey
FROM EXECUTABLE FILE = 'C:\Documents and Settings\All Users\Documents\hunter\StoredProcedures.dll'
CREATE LOGIN SQLCLRTestLogin
FROM ASYMMETRIC KEY SQLCLRTestKey

but I'm receiving error: "Cannot find the asymmetric key 'SQLCLRTestKey', because it does not exist or you do not have permission."

What's wrong?

Best regards...

I recommend you take a look at a sample like the AdventureWorks CLR sample which demostrates all the steps necessary in signing, registering the asymmetric key, registering the login, and finally registering the unsafe assembly. You can find the Yukon SP2 samples here: http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en. Once you've installed the code samples, by default you will find the AdventureWorks CLR sample here: SystemDrive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\AdventureWorks. The readme file for the sample will walk you through each step of the process. You don't need to install any root certificates to use asymmetic keys.

--Bonnie [MSFT]

|||? I'm not a big fan of those instructions. A much, much easier method: First, read this: http://www.sommarskog.se/grantperm.html#certandbulkcopy Follow the instructions therein for setting up a certificate, creating a login using the certificate, etc. But instead of granting bulk copy permissions to the login, grant EXTERNAL ACCESS ASSEMBLY permissions. Backup the certificate and restore it into the database in which you want to create the assembly. Create and catalog the assembly -- no strong name needed (unless you want to use one). Sign your assembly with the same certificate you created in the master DB, and you're good to go. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- ""_hunter"@.discussions..microsoft.com" <"=?UTF-8?B?X2h1bnRlcg==?="@.discussions.microsoft.com> wrote in message news:e74340fa-4ba8-406e-94f4-51698ba38574@.discussions.microsoft.com...Greetings...I'm trying to create assembly with "PERMISSION_SET = UNSAFE".For that I've signed assembly's .dll and installed root certificate to “Trusted Root Certificate Authority.”: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies..aspxnow I'm trying to create login from asymmetric key:USE master GO CREATE ASYMMETRIC KEY SQLCLRTestKeyFROM EXECUTABLE FILE = 'C:\Documents and Settings\All Users\Documents\hunter\StoredProcedures.dll'CREATE LOGIN SQLCLRTestLoginFROM ASYMMETRIC KEY SQLCLRTestKeybut I'm receiving error: "Cannot find the asymmetric key 'SQLCLRTestKey', because it does not exist or you do not have permission."What's wrong?Best regards...|||

BonnieFe, can You be more specific? Ideally just piont on my mistakes...

I've created key by "sn -k SampleKey.snk"

Then in my project's properties i've selected it in Signing->Sign the assembly->Choose a strong name &key file:-><Browse...>

Build->Rebuild Solution

Then in "SQL Server Management Studio":

EXEC('CREATE ASYMMETRIC KEY UnsafeSample_Key FROM EXECUTABLE FILE = ''C:\\StoredProcedures.dll'';');

CREATE LOGIN UnsafeSample_Login FROM ASYMMETRIC KEY UnsafeSample_Key
and I'm reciving the same error: "Cannot find the asymmetric key 'UnsafeSample_Key', because it does not exist or you do not have permission."

|||

NNTP User, I'm trying such:

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'

GO

BACKUP CERTIFICATE UnsafeSample_Certificate TO FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.pvk' ,

ENCRYPTION BY PASSWORD = 'Tomorrow never knows',

DECRYPTION BY PASSWORD = 'All you need is love')

GO

CREATE USER UnsafeSample_Login

FROM CERTIFICATE UnsafeSample_Certificate

GO

GRANT EXTERNAL ACCESS ASSEMBLY

TO UnsafeSample_Login

GO

code, but I'm reciving an error "Cannot find the login 'UnsafeSample_Login', because it does not exist or you do not have permission." on GRANT-command.

|||? Look at your code again -- you've created a USER, rather than a LOGIN -- you need to create the login first, then create a user based on the login, in your database (but not in master). -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- ""_hunter"@.discussions..microsoft.com" <"=?UTF-8?B?X2h1bnRlcg==?="@.discussions.microsoft.com> wrote in message news:46c433b9-51c8-4343-95b2-925ce937dbf4_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com NNTP User, I'm trying such: 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' GO BACKUP CERTIFICATE UnsafeSample_Certificate TO FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.cer' WITH PRIVATE KEY (FILE = 'C:\Documents and Settings\All Users\Documents\hunter\SampleCert.pvk' , ENCRYPTION BY PASSWORD = 'Tomorrow never knows', DECRYPTION BY PASSWORD = 'All you need is love') GO CREATE USER UnsafeSample_Login FROM CERTIFICATE UnsafeSample_Certificate GO GRANT EXTERNAL ACCESS ASSEMBLY TO UnsafeSample_Login GO code, but I'm reciving an error "Cannot find the login 'UnsafeSample_Login', because it does not exist or you do not have permission." on GRANT-command.

Programmatically Enabling sa Login in SS2K5?

Hello.

Our software applications require the sa login to be enabled. We have a .NET (C#) installation application that installs our software applications.

So, is there any way that we can programmatically enable the sa login for SQL Server 2005? Ideally, we'd like to code this into our installation application.

Thanks.

The login is enabled by default if you installed with Mixed Authentication.

You can enable it by executing:

ALTER LOGIN sa ENABLE

Thanks
Laurentiu

Programatically Create User Login

I need to create new user logins that can only get data from a specific
database. I am using the "Create Login" command to do this. Right now, I a
m
working with SQL Server EXPRESS 2005
CREATE LOGIN TestLogin
WITH PASSWORD = 'password',
DEFAULT_DATABASE = TestDB,
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF
This works, as it does create the login. However, when I try to actually
connect with this new login, I receive an error that it can't open the
default database. If i connect as the SA, and go to the "User Mapping" for
the new login (in this case "TestLogin"), no databases are checked. If I
check this login's default database (in this case "TestDB"), then I am able
to re-connect as the new login ("TestLogin") and am able to get data from th
e
default database ("TestDB").
Can someone explain what I am missing to programatically create this login,
assign a default database and have it work?
Thank you
MATTthe next step is
use TestDB
go
create user TestLogin for TestLogin
go
you can login now using the TestLogin acct.
hth,
"MATT" <MATT@.discussions.microsoft.com> wrote in message
news:38F2CB3D-2F2A-4D92-A737-CA459C3A01A5@.microsoft.com...
>I need to create new user logins that can only get data from a specific
> database. I am using the "Create Login" command to do this. Right now, I
> am
> working with SQL Server EXPRESS 2005
> CREATE LOGIN TestLogin
> WITH PASSWORD = 'password',
> DEFAULT_DATABASE = TestDB,
> CHECK_EXPIRATION = OFF,
> CHECK_POLICY = OFF
> This works, as it does create the login. However, when I try to actually
> connect with this new login, I receive an error that it can't open the
> default database. If i connect as the SA, and go to the "User Mapping"
> for
> the new login (in this case "TestLogin"), no databases are checked. If I
> check this login's default database (in this case "TestDB"), then I am
> able
> to re-connect as the new login ("TestLogin") and am able to get data from
> the
> default database ("TestDB").
> Can someone explain what I am missing to programatically create this
> login,
> assign a default database and have it work?
> Thank you
> MATT
>