I want to have a function available to multiple apps that creates connections to our sql server 7 database (they do not want to upgrade to 2000 or 2005 yet). My questions are:
Is it safe to put functions that connect to the db in a web service?
Should I have any additional security measures in place?
Here is a sample of one I converted to be potentially used:
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data.SqlClient
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class WebService
Inherits System.Web.Services.WebService
Public globConnection As SqlConnection
Public gstrDBName As String
Public gstrSwitchboardPath As String
Public gstrHelpPath As String
<WebMethod()> _
'This Module is the ONLY access to the SQL Server
'It is using NT Authentication to permit access
'If the user is not a Valid NT user access is Denied
'If the user has not been granted permission to the SQL server access will be Denied
Public Function ConnectToDB() As Boolean
Dim BoolState As Boolean
Dim strConnection As String
gstrDBName = "DBNAME"
strConnection = "Provider=SQLOLEDB.1;" & _
"Data Source= SERVERNAME;Initial Catalog= " & gstrDBName & ";" & _
"Integrated Security=SSPI "
On Error Resume Next
If globConnection Is Nothing Then
globConnection = New SqlConnection
End If
If globConnection.State = Data.ConnectionState.Open Then
BoolState = True
Else
globConnection = New SqlConnection
globConnection.ConnectionString = strConnection
globConnection.Open()
If globConnection.State = Data.ConnectionState.Open Then
BoolState = True
Else
BoolState = False
End If
End If
ConnectToDB = BoolState
End Function 'ConnectToDB
End Class
Any input, suggestions, etc would be greatly appreciated.its fine so long as the public methods you expose through your webservice don't open the door for someone to send in malicious code..
for example, if your webservice exposed a routine that allowed you to pass a SQL string to it and run that against your DB, that would be a bad risk, because someone could easily pass a string to delete all your tables.
You may also want to disallow public access to your service or have authentication of some sort for the users of the service.
We use all stored procedures, no hard coding SQL into any of our code. Our VB 6.0 security Model looks something like this currently:
Database
Stored Procedure
Class
Module
Application
I am trying to figure out how to implement similar security layers in ASP 2.0 and be able to use alot of the newer functionality, improve scalability (they want to roll this stuff out to multiple US and International - eventually - clients) without having to install cumbersome applications that require local support when users hose them up entirely or have to worry about how much memory, etc they have. Connectivity is not an issue because if they lose connectivity to us it does not matter what they use. I have been studying this article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/secnetch03.asp
If there are any other decent articles out there, can I get links to them?
Thank you very much.
hows about this one
http://msdn2.microsoft.com/en-us/library/w67h0dw7.aspx
Ok, I have security squared away...I think hehe. Now I run into a bigger problem - connecting a gridview to SQL Server 7.0. I know that it cannot be done directly and I have to use a dataset, however I cannot seem to figure out the right configuration of parameters and/or syntax to make it happen. Any help? Here is what I have so far that is not utterly ridiculous:
Public globConnection As SqlConnection
Public globDS As SqlDataSource
Public Function ConnectToDS() As SqlDataSource
Dim strConnection As String
gstrDBName = "DBNAME"
strConnection = "Provider=SQLOLEDB.1;" & _
"Data Source= ServerName;Initial Catalog= " & gstrDBName & ";" & _
"Integrated Security=SSPI "
If globConnection Is Nothing Then
globConnection = New SqlConnection
End If
globDS.ConnectionString = strConnection
ConnectToDS = globDS
End Function
I have been and continue to look on the web. The only thing I have found a reference to is caching...not alot of detail just reference. Again, any suggestions, pointers and guidance is muchly appreciated.
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.aspx
