July 20, 2008 at 8:49 pm
Well, certificates are what MS is trying to push everyone towards. However, that is even more complicated and confusing, IMHO.
[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]
July 21, 2008 at 7:57 am
I'm using VB6 with ADO.
July 21, 2008 at 1:48 pm
Jacob Pressures
Here is my code to use a stored procedure to return a Record set.
Public Cnn As ADODB.Connection
Public Cnn_String As String
Public cmd_Param as ADODB.Parameter
Public Rs as ADODB.Recordset
Set Ado_Cmd = New ADODB.Command
Ado_Cmd.CommandText = 'Sp_SpaceUsedAll" -- is the name of an existing stored procedure in the database.
Ado_Cmd.CommandType = adCmdStoredProc
If Cnn.State <> adStateOpen Then
Cnn.ConnectionString = Cnn_String
Cnn.Open
End If
Ado_Cmd.ActiveConnection = Cnn
Set Rs = New ADODB.Recordset
Rs.Open Ado_Cmd, , adOpenStatic, adLockReadOnly
If you are using procedures that do not have a variable value insterted in the string VB is processing that is all that is to it.
If you do use a variable then use:
Set cmd_Param = Ado_Cmd.CreateParameter("@Watchword", adVarChar, adParamInput, 50) ', "Select") Ado_Cmd.Parameters.Append cmd_Param
cmd_Param = your value.
Set Rs = New ADODB.Recordset
Rs.Open Ado_Cmd, , adOpenStatic, adLockReadOnly
In modernizing others code what I did was run in debug mode with a break point just before the code executed the SQL statement. Displayed the SQL statement in the debug window, copied it and used it to create in the SQL database a stored procedure.
Follow my "cheat" technique and you can change your code a little bit at a time to use only stored procedures.
Check "C:\Program Files\Microsoft SQL Server\80\Tools\Books\adosql.chm" - part of a complete install of SQL 2000, and a fantastic resource for using ADO to communicate with SQL Server.
July 21, 2008 at 3:46 pm
Jacob Pressures (7/21/2008)
I'm using VB6 with ADO.
In that case, I'd suggest that you stick with AppRoles. When combined with good stored procedure security, they provide very good ROI, security-wise. Plus, I am not sure how much support for ceritificates and exe-signing there is prior to .Net.
[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 4, 2008 at 8:02 pm
I'm using SS2005 and VB6. No store procedures yet. I created the approle but i still have not placed all the necessary information in my app to connect to the server.
Here is the error i get.
Error: #1
Date: 9/4/2008 10:24:59 AM
ADO Error: #-2147467259
Description: Login failed for user ''. The user is not associated with a trusted SQL Server connection.
Source: Microsoft OLE DB Provider for SQL Server
Here is some sample code from VB6 app i wrote.
'Declarations Section
Dim adoConnection As ADODB.Connection 'Used to establish connection with the Employee Database
Dim adoRecordset As ADODB.Recordset 'Used to query the Employee table
Dim connString As String 'Stores connection string
Dim SQLstr As String 'Stores query
Dim iResponse As Integer 'Stores response from message box
Dim boolSaveToDB As Boolean 'Stores value indicating whether to save to DB or not
Dim x As Integer 'Counter for For Loop
Dim sPreviousLastName As String 'Used in case of change in last name
On Error GoTo AdoErrors
'Establish connection with FAE database
Set adoConnection = New ADODB.Connection
connString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=FAE;Data Source=fp-pi1"
adoConnection.Open connString
'Verifies database connection
If adoConnection.State = adStateOpen Then
'Debug.Print "Connection was established"
SQLstr = "SELECT e.EmpID, e.FirstName, e.LastName, RTrim(e.BadgeNumber), e.DeptID, e.CenterID From Employees e WHERE RTrim(e.BadgeNumber) = " & sBadge & " AND e.Inactive = 0"
'Create Recordset Object and Search Employee Database
Set adoRecordset = New ADODB.Recordset
With adoRecordset
.Open SQLstr, adoConnection, adOpenKeyset, adLockOptimistic, adCmdText
'Gather employee information
'Code goes here
'Close recordset and connection
adoRecordset.Close
Set adoRecordset = Nothing
adoConnection.Close
Set adoConnection = Nothing
SearchEmployeeDatabase = vbCancel 'private function
End If
End If
Exit Function
AdoErrors:
'Errorhandling Declarations
Dim errCollection As ADODB.Errors 'ADO Errors Object
'In case the adoConnection is not established or there were other initiation problems
On Error Resume Next
'private function
SearchEmployeeDatabase = vbNo
Set errCollection = adoConnection.Errors
ErrorLog errCollection
End Function
September 4, 2008 at 8:13 pm
Do i need all of this code and how do i implement it into my code above?
Sample code from BOL
DECLARE @cookie varbinary(8000);
EXEC sp_setapprole 'Sales11', 'fdsd896#gfdbfdkjgh700mM'
, @fCreateCookie = true, @cookie = @cookie OUTPUT;
-- The application role is now active.
SELECT USER_NAME();
-- This will return the name of the application role, Sales11.
EXEC sp_unsetapprole @cookie;
-- The application role is no longer active.
-- The original context has now been restored.
GO
SELECT USER_NAME();
-- This will return the name of the original user.
GO
I understand this part of the code
EXEC sp_setapprole 'Sales11', 'fdsd896#gfdbfdkjgh700mM'
but the rest I don't completely understand how it works.
thanks guys!
September 4, 2008 at 8:25 pm
Jacob Pressures (9/4/2008)
Here is the error i get.Error: #1
Date: 9/4/2008 10:24:59 AM
ADO Error: #-2147467259
Description: Login failed for user ''. The user is not associated with a trusted SQL Server connection.
Source: Microsoft OLE DB Provider for SQL Server
This error means that you attempted to connect to SQL Server using your windows account, but it is not authorized to access SQL Server.
[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 4, 2008 at 8:37 pm
This may be caused by the fact that I used my laptop not apart of the network to access the server. I did this because of difficulties using my work computer. We have so many restrictions and so much politics i just use my laptop. (This is "approved" or recommended somehow although in my opinion it creates more risk than my using my work computer on the domain.)
I can try using my work computer, but I'm sure that SS2005 will be looking for a password in my application either way. So i may not get this error but a different one is coming.
Thanks!
September 4, 2008 at 9:43 pm
I see the point you were making more clearly here. I didn't think it would work but i don't know anything about networking and the network admin is going to Iraq on September 19. He told me just use my laptop. I told him i would need better access. I understood the basics behind Win Authentication but since i could login to the Project server i wasn't sure if i would need to be authenticated by at the PC level. Now i know. that makes sense anyway since the app is running from my PC. It might be different if i installed it on the server itself.
When you connect, the SQL Server 2000 client software requests a Windows trusted connection to SQL Server 2000. Windows does not open a trusted connection unless the client has logged on successfully using a valid Windows account. ... SQL Server 2000 gets the user account information from the trusted connection properties and matches them against the Windows accounts defined as valid SQL Server 2000 logins. If SQL Server 2000 finds a match, it accepts the connection. When you connect to SQL Server 2000 using Windows 2000 Authentication, your identification is your Windows NT or Windows 2000 group or user account.
September 5, 2008 at 7:02 am
The application role and control of same has changed from 2000 in 2005.
Reading from BOL it would appear that you set up logins for each user of the application, and then assign each of these users to a role. I would call the role "Donuttin". Then set what users can do in this role denying them the ability to do anything what so ever in the database.
SQL Server 2005 Books Online (September 2007)
Application Roles
Connecting with an Application Role
The following steps make up the process by which an application role switches security contexts:
A user executes a client application.
The client application connects to an instance of SQL Server as the user.
The application then executes the sp_setapprole stored procedure with a password known only to the application.
If the application role name and password are valid, the application role is activated.
At this point the connection loses the permissions of the user and assumes the permissions of the application role.
The permissions acquired through the application role remain in effect for the duration of the connection.
In earlier versions of SQL Server, the only way for a user to reacquire its original security context after activating an application role is to disconnect and reconnect to SQL Server. In SQL Server 2005, sp_setapprole has a new option that creates a cookie that contains context information before the application role is activated. The cookie can be used by sp_unsetapprole to revert the session to its original context. For information about this new option and an example, see sp_setapprole (Transact-SQL).
From BOL sp_setapprole (Transact-SQL)
The following example activates the Sales11 application role with password fdsd896#gfdbfdkjgh700mM, and creates a cookie. The example returns the name of the current user, and then reverts to the original context by executing sp_unsetapprole.
DECLARE @cookie varbinary(8000);
EXEC sp_setapprole 'Sales11', 'fdsd896#gfdbfdkjgh700mM'
, @fCreateCookie = true, @cookie = @cookie OUTPUT;
-- The application role is now active.
SELECT USER_NAME();
-- This will return the name of the application role, Sales11.
EXEC sp_unsetapprole @cookie;
-- The application role is no longer active.
-- The original context has now been restored.
GO
September 5, 2008 at 8:27 am
Thanks for the posts guys. I started out using SS2000 then my network admin switched me over to SS2005 because he was having problems with SS2000 for some reason. So all my books and everything are in 2000.
I've read what you said about creating a login for each user. I don't understand that. Is that SS2000 or SS2005 or both?
I also read that even though i have admin access to this server, I have to include myself into a role along with the other users.
My understanding is simply this:
- Log in using WAM
- Open application
- App gains access to DB
- DB uses WAM to verify that the user is valid
- App and user gains access.
This evidently is NOT complete. What i'm understanding is that i need to set permissions for everyone in my company. I have no idea how to get access to all those people. I guess I'll have to work through the network admin.
I dont see the connection between the database permissions and granting the approle permissions either. I'm going to see what I can find. I've read over my SS2000 books several times and they are not straightforward. I've not read anything that goes into all these details.
Thanks!
September 5, 2008 at 8:30 am
Just clarification:
bitbucket (9/5/2008)
The application role and control of same has changed from 2000 in 2005.Reading from BOL it would appear that you set up logins for each user of the application, and then assign each of these users to a role.
You can assign users to a database role. You cannot assign users to an application role. However, a user session that has the password can switch its context to that application role.
[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 5, 2008 at 9:04 am
rbarryyourg
and then assign each of these users to a role. I would call the role "Donuttin".
I meant to set the users to the Donuttin role which is a database role, sorry I was not clear enough
September 5, 2008 at 9:12 am
Jacob Pressures download Books On Line from:
September 7, 2008 at 12:00 pm
Sorry guys, I didn't realize you had responded. Looks like I overlooked it in the mist of some junk.
Ok, So I create a db role DoNuttin and of course, i assign all users no privileges. Do i assign myself to this same role?
Every time a new user comes into the company, will I have to add them also to the DB role? (just seems crazy)
Now how do i configure the connection string to send approle name and password?
I'm not understanding where in the connection string that goes.
Guys Thanks a MILLION!
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply