Login failed for user 18456 when using asymmetric key for login

Hi,

I wanted to check the asymmetric key option in sql 2005. I copied the books online code for creating a asymmetric key and then used this key for creating a login. Now when I try to login without entering any password I am receiving 18456 error. I would like to know what I am missing here. If I use CREATE LOGIN from asymmetric key or certificate how do I login and with what credentials. Do I need to provide any password.

CREATE ASYMMETRIC KEY PacificSales09 WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = 'bmsA$dk7i82bv55foajsd9764'; GO

CREATE LOGIN asm FROM ASYMMETRIC KEY PacificSales09;

Regards,

Ravi

After Creating a login you will have to give explicit permissions of connectivity and other things to the login, was that done?|||

Hi,

Thanks for the response. Yes I did create a user for the login and put in the reader/writer role. So that was not the issue here.

Even if I do not grant these permissions by default it should still have permissions that a Guest login would have so I would still be able to get connected to the server. Same stuff works for other logins I have the problem only when I create a login from certificate and asymmetric key sources.

I think I do not have to supply a password for my login when I create it using certificate and asymmetric key sources.

I still do not understand how do I use a login created from these sources and what difference does it make from a normal login.

Regards,

Ravi

|||

Logins mapped to certificates or asymmetric keys cannot be used for authentication with SQL Server - you cannot login with them. These logins are used to assign server-level permissions to the certificates/asymmetric keys that are mapped to them, which is useful for signing; that is their sole purpose. For an example of use, see the following example:

http://blogs.msdn.com/lcris/archive/2005/06/15/429631.aspx

Thanks
Laurentiu

|||

Thanks a lot. It helped me in understanding their usage.

Regards,

Ravi

Login failed for user 18456 when using asymmetric key for login

Hi,

I wanted to check the asymmetric key option in sql 2005. I copied the books online code for creating a asymmetric key and then used this key for creating a login. Now when I try to login without entering any password I am receiving 18456 error. I would like to know what I am missing here. If I use CREATE LOGIN from asymmetric key or certificate how do I login and with what credentials. Do I need to provide any password.

CREATE ASYMMETRIC KEY PacificSales09 WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = 'bmsA$dk7i82bv55foajsd9764'; GO

CREATE LOGIN asm FROM ASYMMETRIC KEY PacificSales09;

Regards,

Ravi

After Creating a login you will have to give explicit permissions of connectivity and other things to the login, was that done?|||

Hi,

Thanks for the response. Yes I did create a user for the login and put in the reader/writer role. So that was not the issue here.

Even if I do not grant these permissions by default it should still have permissions that a Guest login would have so I would still be able to get connected to the server. Same stuff works for other logins I have the problem only when I create a login from certificate and asymmetric key sources.

I think I do not have to supply a password for my login when I create it using certificate and asymmetric key sources.

I still do not understand how do I use a login created from these sources and what difference does it make from a normal login.

Regards,

Ravi

|||

Logins mapped to certificates or asymmetric keys cannot be used for authentication with SQL Server - you cannot login with them. These logins are used to assign server-level permissions to the certificates/asymmetric keys that are mapped to them, which is useful for signing; that is their sole purpose. For an example of use, see the following example:

http://blogs.msdn.com/lcris/archive/2005/06/15/429631.aspx

Thanks
Laurentiu

|||

Thanks a lot. It helped me in understanding their usage.

Regards,

Ravi

Logic for picking values from columns

Hi
Can anyone please help me ceate a logic (and SQL syntax) for this. I
have a table which has the 1st column as some Primary key and the rest
of the columns has integer values stored in them (there may be any no.
of columns with the integer values).
Now, I want to read the values in each record one by one for diff
columns starting from the 1st column with integer values, and pick the
column name of 1st non-zero integer.
Please see if someone can help with this.
Thanks
SGSG
CREATE TABLE #Test
(
rowid INT NOT NULL,
col1 INT,
col2 INT,
col3 INT
.....
)
So far everything is ok ,now I'm not sure understood you
DECLARE @.var VARCHAR(50)
SET @.var=''
SELECT @.var=@.var+ COALESCE(col1,0)+','+COALESCE(col2,0)+...... FROM #Test
SELECT @.var
--Or did you mean to get all values under one (the first one column)?
SELECT col1 FROM #Test
UNION ALL
SELECT col2 FROM #Test
UNION ALL
........
If it does not help please post desired output.
"SG" <shekhar.gupta@.gmail.com> wrote in message
news:1137998791.360110.248740@.g47g2000cwa.googlegroups.com...
> Hi
> Can anyone please help me ceate a logic (and SQL syntax) for this. I
> have a table which has the 1st column as some Primary key and the rest
> of the columns has integer values stored in them (there may be any no.
> of columns with the integer values).
> Now, I want to read the values in each record one by one for diff
> columns starting from the 1st column with integer values, and pick the
> column name of 1st non-zero integer.
> Please see if someone can help with this.
> Thanks
> SG
>|||SG wrote:
> Hi
> Can anyone please help me ceate a logic (and SQL syntax) for this. I
> have a table which has the 1st column as some Primary key and the rest
> of the columns has integer values stored in them (there may be any no.
> of columns with the integer values).
> Now, I want to read the values in each record one by one for diff
> columns starting from the 1st column with integer values, and pick the
> column name of 1st non-zero integer.
> Please see if someone can help with this.
> Thanks
> SG
"Pick the column name" I assume means you want to return the first
column name for each row? If I'm wrong then please post DDL, sample
data and show your required end result so that we don't have to guess
again.
Try:
SELECT
CASE
WHEN col1<>0 THEN 'col1'
WHEN col2<>0 THEN 'col2'
WHEN col3<>0 THEN 'col3'
.. etc
END AS col
FROM your_table ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks Uri / David,
I have got the idea how to do this. I am sorry about not posting the
table structure and the code, will take care abt this in future.
Regards,
SG