encryption in approle - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

encryption in approle

Author

Topic

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-08-28 : 10:31:58

Hi all,

I am trying to setapprole and the below command works:

Create application role test with PASSWORD = 'test'

EXEC sp_setapprole 'test',N'test'

I am not sure, if the password will be encrypted by the above method or if the password will be stored as plain text.

I tried the below, and I got an error:


EXEC sp_setapprole 'test',{encrypt N'test'}, odbc

ERROR:
'encrypt' is not a recognized ODBC date/time extension option.

Please give me your suggestions.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-28 : 10:45:37

EXEC sp_setapprole 'test',N'test'--> will not encrypt password

try EXEC sp_setapprole 'test',{encrypt N'test'}, 'odbc'--> this will encrypt your password.

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-08-28 : 10:51:49

Yes I tried that and got the below error


EXEC sp_setapprole 'test',{encrypt N'test'}, 'odbc'

'encrypt' is not a recognized ODBC date/time extension option.

Is there any other I can encrypt the password?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-28 : 10:58:14

you have to be using an ODBC client for this.. or else it won't work.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-28 : 10:59:32

ODBC client or OLEDB provider for sql server..

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-08-28 : 11:18:32

OK. I just set up the ODBC data source for my sqlserver. How can I connect to the database with ODBC using this????

Thanks much for your help

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-28 : 11:36:24

What application are you using ??

refer knowledge docs or MSDN..
This link hopefully helps you->

http://msdn.microsoft.com/en-us/library/aa215439(SQL.80).aspx

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-08-28 : 15:02:01

I just found that the application is connecting to the database using ODBC. So I think this will work.

Im trying to test the query from Excel using ODBC.

EXEC sp_setapprole 'test',{encrypt N'test'}, 'odbc'

But I am not able to run the query from excel. I am able to see the records in the table and change the select statements for all the tables in the database. But I am not able to run this exec command from Excel.

Does anybody know how to run this command from Excel?? Please help. Thanks a lot.

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-08-28 : 15:16:40

Also, is it possible to exec sp_setapprole from within a stored procedure?? I think I our application does not allow {} directly..

Please give me your comments. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources