August 28, 2008 at 12:34 pm
Try editing the Connection properties and on the Definition tab, edit the Command Text and put the following in front of the text that is already there:
EXEC sp_setapprole 'test',{encrypt N 'test'}, 'odbc';
Though I guess it will really depend on what version of Excel and how your connection is setup in Excel and how it is being used (there are many different ways these things could be setup in Excel).
[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 1:09 pm
Hmm, no success yet ! I am able to change the other regular sql commands in the command text space but not this 'exec' command. It just throws the same error that the table is not opened.
Next step, I am planning to try running as stored procedure from the application instead of 'exec sp_setapprole'.
Do you have any idea on that? I am sorry I am bothering you a lot today;)
August 29, 2008 at 2:19 am
first of all ... from BOL 2005 :
[ @password = ] { encrypt N'password' }
Is the password required to activate the application role. password is sysname, with no default. password can be obfuscated by using the ODBC encrypt function. When you use the encrypt function, the password must be converted to a Unicode string by placing N before the first quotation mark.
The encrypt option is not supported on connections that are using SqlClient.
Important:
The ODBC encrypt function does not provide encryption. You should not rely on this function to protect passwords that are transmitted over a network. If this information will be transmitted across a network, use SSL or IPSec.
@encrypt = 'none'
Specifies that no obfuscation be used. The password is passed to SQL Server as plain text. This is the default.
@encrypt = 'odbc'
Specifies that ODBC will obfuscate the password by using the ODBC encrypt function before sending the password to the SQL Server Database Engine. This can be specified only when you are using either an ODBC client or the OLE DB Provider for SQL Server.
So once SQLNCLI gets involved, you can nolonger use the encrypt stuff.
- There is a difference in concept of an application role and a stored procedure.
An application role is used to grant a user authority only with after having activated the application role. (login only needs connect to database )
- If a user has been granted exec of a sproc, there is nolonger the check if it is executed using the application.
So a user need to know the application role name and its password (code sniffing) to be able to switch to it and only then he can do some stuff.
We have an application using application roles, the application roles are only being granted execute permission for certain sprocs.
We do not use the encrypt option.
Application load modules are being dotfuscated.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 29, 2008 at 2:41 am
Here's an example that can be executed using your sql management studio in SQLCMD mode.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 10, 2008 at 12:44 pm
Hi all,
I am back again. Not sure if it just the application issue. We are trying to exec sp_setapprole without the encrypt option now by passing all the parameters (cookie) because we are hoping the application is looking at all the input parameters.
The command is successful when I execute from sql server. But from the application trace code, we see that it is expecting another input parameter.
Under Master database under sys.sp_setapprole under parameters, I see the below:
@rolename, @password, @encrypt, @fcreatecookie, @cookie ,Returns integer.
Is there a way just to add this parameter for encrypt, dummy way like @fCreateCookie = false????
Just to see alteast if the application works?
Thanks a lot
September 10, 2008 at 5:02 pm
@encrypt = 'none'
[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]
September 10, 2008 at 5:08 pm
but @encrypt is an optional parameter, you shouldn't have to specify it.
[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]
September 11, 2008 at 7:49 am
Don't confuse the Create_Approle stored procedure with sp_setApprole. When you execute the Create_Approle procedure, you are creating an application role under the roles for the database you are working on. Once created, that role is there and you should get an error if you run it again indicating that the role already exists. A previous post outlined the process of creating an application role very nicely.
To answer one of your other questions, yes, you must provide all of the credentialing information to the sp_setapprole stored procedure from within your application code.
If your using VB the following example might help (and this also works in ASP.NET).
private sub connectToSQLServer()
' Set up a connection string.
' Dim ConnectionString as string = "server=[servername];Integrated Security=SSPI;Initial Catalog=[database name];"
' I use the followining to create a connection string
Dim connectionString as string = "server=[servername];user=guest;pwd=;Initial Catalog=[database name];"
' Guest is already a default user for SQL Server and should have at the minimal, connect access.
' You could give guest a password and that would add another layer of security!
' Set up the Connection object.
Dim obj_cn as new system.data.sqlclient.sqlconnection(ConnectionString)
Try
obj_cn.open()
' Now that we have an open connection, create a command object
Dim obj_cmd As New System.Data.SqlClient.SqlCommand("sp_setapprole", obj_cn)
' This object will have a CommandType of CommandType.StoredProcedure
obj_cmd.CommandType = CommandType.StoredProcedure
' Add the required parameters to the command object.
obj_cmd.Parameters.Add("@rolename", Data.SqlDbType.VarChar).Value = "[yourapprolename]"
obj_cmd.Parameters.Add("@password", Data.SqlDbType.VarChar).Value = "[rolepassword]"
obj_cmd.Parameters.Add("@encrypt", Data.SqlDbType.VarChar).Value = "none"
' Execute the stored procedure sp_setAppRole with the parameters we just added.
obj_cmd.ExecuteNonQuery()
' Some cleanup is required to dispose of unused objects.
obj_cmd = Nothing
cn.close()
cn.dispose()
Catch sys_ex As System.Exception
' trap any errors that may occure.
msg.Text = "Error getting the data...." & sys_ex.Message()
End Try
End Sub
Now, this is just a generic example so experiment with it. Also note the use of try and catch - this is much cleaner when you have to deal with errors and it allows you to customize the error messages that are returned by your application. I also used unencrypted passwords since my apps all run in an intranet/local environment. Had I written this to go to an external site (i.e. An internet web site), I would have used encrypted passwords. Make no mistake about this - it gets really fussy with using encrypted passwords, so make sure you have everything set up properly before making the call!
Some other things that I have experienced when using application roles:
1. In a VB Windows Forms application, set your application role once and make all of your calls to the database through stored procedures. This will save you hours of adminstrative work. The application role will need to have execute privilages for each stored procedure that your application uses and thats all. Maintaining the queries in a stored procedure call is much easier than recompiling your code everytime you make a change to your SQL Statement plus you don't have to grant privilages for your tables and views to the application role. Once you create a stored procedure, give the role execute permission for that stored procedure and your done!
2. In ASP.NET I found that for each call to the database, I had to execute the sp_setapprole before I made the call. Not sure why but it must have something to do with the way ASP.NET handles database calls.
3. In ASP.NET Web applications having your SQL Statement in your code opens you up to SQL Injections and unless you do some really fancy coding to trap and test the SQL Before it's executed, you could have a real problem on your hands. Again, use Stored Procedures - much cleaner, easier to maintain, and it hides the underlying database objects from the world.
So, thats what I know about sp_setapprole, if you find a better way, let me know! 😛
I know this is lengthy but I think the extra effort of giving out examples helps to clarify some of the unknowns.
September 11, 2008 at 8:05 am
Thanks a lot. This helps.
September 11, 2008 at 9:23 am
Silly me, I didn't read through to the end of the posts before sending my last response. The example I provided is for windows applications developed using Visual Studio or Visual Web Developer. As for Excel, the syntax may be different since it is using ODBC and to be honest, I have not tried it in that sense. I just thought this warrented a clarification on where to use the code. If this works in Excel, then great otherwise you may need to do some more adjustments before it all works.
Sorry if I created any confusion.
Dave
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply