August 22, 2008 at 7:52 am
Wouldn't it be easier to create a SQL login that would only be used by the application, instead of a windows user? How do users log on as a different windows user? They would have to use something like "run as".
Create a SQL login and assign it the rights it needs. Have the application connect to SQL Server using that login. This way there's no connections between what the users can access via the application and what they can access without it.
Alvin,
Not every company wants to use SQL logins, as they can be an audit issue.
----------------------
https://thomaslarock.com
August 28, 2008 at 8:00 am
Rbarry, I tried the below command to exec setapprole it was successful.
EXEC sp_setapprole 'test', N'test';
But, is the password encrypted in this method or do I have to mention encrypt?
Below is the command I used for password encryption and I am getting the below error:
EXEC sp_setapprole 'test',{encrypt N'test'}, odbc
ERROR:'encrypt' is not a recognized ODBC date/time extension option.
Any clue?? Thank you.
August 28, 2008 at 8:24 am
Does the below command store the password in encrypted format or plain text??
EXEC sp_setapprole 'test', N'test';
Please give me your suggestions. Thanks
August 28, 2008 at 8:36 am
Sadly, I have never had the opportunity to implement the ODBC Encryption feature, so I cannot help you much. Hopefully, someone else here can. If you do figure this out though, please report back on how to do it, because I would like to know also. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 8:41 am
Aww okk.. But does it store the password in plain field if we execute this command?
EXEC sp_setapprole 'test',N'test'
And do you know where the password is stored???
Thanks a lot.
August 28, 2008 at 8:51 am
Actually, I do notice one difference between your code and the BOL Example. This line:
psangeetha (8/28/2008)
EXEC sp_setapprole 'test',{encrypt N'test'}, odbc
Is actually like this in the example:
EXEC sp_setapprole 'test',{encrypt N 'test'}, odbc
Note the space after the "N". Apparently it is a seperate argument and not an NChar text flag.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 9:00 am
Rbarry, It didnt work either.. same error...
EXEC sp_setapprole 'test',N'test'
Are you using this method currently without encryption?? How does it store the password?? plain text?? and do you where it stores the password??
THanks again
August 28, 2008 at 9:01 am
psangeetha (8/28/2008)
Does the below command store the password in encrypted format or plain text??[codee]EXEC sp_setapprole 'test', N'test';[/code]
Plain text. By the way the ODBC Encrypt does not store the password, it simply encrypts it when it is passed over the network to your SQL Server.
Passwords are always stored in an encrypted/hashed form on SQL Server. The ODBC Encrypt just makes sure that it is also encrypted while it is being transmitted to the SQL Server. As for it's storage in your Client app or WebServer, that is still in plain text, unless you do something about it there.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 9:11 am
THanks Rbarry. THat helps.
ANd if anyone else have used SSL or IPSec for encryption, please let me know. THanks.
August 28, 2008 at 9:11 am
psangeetha (8/28/2008)
Are you using this method currently without encryption??
I have seen it both ways, though I myself may not be actually "using" it. Most of the time I am working for my customers' Server or Database departments, not their Development departments. So I usually see this from the DBA side, the developers only come to me if they cannot figure something out, which naturally, they do not want to admit. 😉 So they usually work it out for themselves.
So I see it, I have recommended it, I have implemented it (for myself), I have written guidelines for it, but I have not actually written the ODBC Encrypt part yet.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 9:27 am
Hmm, okay..
I just setup the ODBC data source for the server.
Not sure how I can connect to the server with ODBC..
I appreciate any help. Thanks
August 28, 2008 at 9:37 am
As I recall in .Net, you need to switch from SQLClient.net to ODBCClient. (not sure?)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 10:44 am
Hmm, atlast I just found out that the application that is connecting to the database is using ODBC. So if we can run the
EXEC sp_setapprole 'test',{encrypt N 'test'}, 'odbc'
from the application code It should work, right?
Thanks.
August 28, 2008 at 10:51 am
As far as I know.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 11:49 am
Ok. I think it should work. I am trying to just test it from Excel using ODBC.
I set up ODBC and I am able to connect to the database from Excel. But it is only importing data from the tables and I am not sure where I can run the
EXEC sp_setapprole 'test',{encrypt N 'test'}, 'odbc'
from Excel.
Please give me your suggestions. Thanks.
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply