Problem connecting to SQL server in web application

[hermandup]

I'm busy writing my first proper web application in VB.NET, however there's no way I can manage to get my app to simply connect to our SQL server. It always gives me the error: SQL server does not exist or access denied.
Just to make sure, I made a Windows application in VB.NET wich uses the exact same code to connect to the SQL server, and it works 100%, so it's not my connectionstring and it's not .NET and the server does exist.
After many hours on the Internet, I found there's many other people with the same problem and I've tried all their solutions, but nothing works. This is what I've tried:

- Checked that the server is not set to use Windows authentication, but server authentication.
- Checked that Windows' firewall is set to allow requests from port 1433, TCP.
- I created a user aspnet on the SQL Server for my database.

If there's anyone that can shed more light on this issue, please help me. I'm beyond desprate!!

Thanks!!

 

[kulrom]

Can you please send the code from the windows app (the one which works) as well as the code from the web application?

regards

 

[hermandup]

Hi. Thanks for the reply.

Here's the code from the Windows app, which works fine. I call the GetUserByUsername function.

[SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data.SqlClient
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE][SIZE=2] Users
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] UserData [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Data.DataTable()
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] UserDataSet [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Data.DataSet()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Conn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection(SystemVars.ConnectionString)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Comm [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Adpt [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlDataAdapter()
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2]()
UserData.Clear()
UserData.TableName = "UserMaintenance"
UserData.Columns.Add("Username", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](System.String))
UserData.Columns.Add("Name1", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](System.String))
UserData.Columns.Add("Name2", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](System.String))
UserData.Columns.Add("Surname", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](System.String))
UserData.Columns.Add("PasswordText", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](System.String))
UserData.Columns.Add("DepartmentID", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](System.Int32))
UserData.Columns.Add("DepSectionID", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](System.Int32))
UserData.Columns.Add("UserTypeID", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](System.Int32))
UserData.Columns.Add("Admin", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](System.Int16))
UserDataSet.Tables.Add(UserData)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE] 
[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE][SIZE=2] GetUserByUsername([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] pUsername [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2]) [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2]GetUserByUsername = ""
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]Conn.Open()
Comm.Connection = Conn
Comm.CommandText = "SELECT * FROM UserMaintenance WHERE Username = '" & pUsername & "'"
Adpt.SelectCommand = Comm
Adpt.Fill(UserData)
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] UserData.Rows.Count = 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]GetUserByUsername = "User not found"
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
GetUserByUsername = ex.ToString
[/SIZE][SIZE=2][COLOR=#0000ff]Finally
[/COLOR][/SIZE][SIZE=2]Conn.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#000000] [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Class
[/COLOR][/SIZE][/COLOR][/SIZE]

Here's the code from the Web app that does not work, but it's exactly the same code:

[SIZE=2][COLOR=#0000ff]
Imports[/COLOR][/SIZE][SIZE=2] System.Data.SqlClient
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE][SIZE=2] Users
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] UserData [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Data.DataTable()
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] UserDataSet [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Data.DataSet()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Conn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection(SystemVars.ConnectionString)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Comm [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Adpt [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlDataAdapter()
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2]()
UserData.Clear()
UserData.TableName = "UserMaintenance"
UserData.Columns.Add("Username", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](System.String))
UserData.Columns.Add("Name1", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](System.String))
UserData.Columns.Add("Name2", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](System.String))
UserData.Columns.Add("Surname", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](System.String))
UserData.Columns.Add("PasswordText", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](System.String))
UserData.Columns.Add("DepartmentID", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](System.Int32))
UserData.Columns.Add("DepSectionID", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](System.Int32))
UserData.Columns.Add("UserTypeID", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](System.Int32))
UserData.Columns.Add("Admin", [/SIZE][SIZE=2][COLOR=#0000ff]GetType[/COLOR][/SIZE][SIZE=2](System.Int16))
UserDataSet.Tables.Add(UserData)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
 
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Function[/COLOR][/SIZE][SIZE=2] GetUserByUsername([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] pUsername [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2]) [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2]GetUserByUsername = ""
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]Conn.Open()
Comm.Connection = Conn
Comm.CommandText = "SELECT * FROM UserMaintenance WHERE Username = '" & pUsername & "'"
Adpt.SelectCommand = Comm
Adpt.Fill(UserData)
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] UserData.Rows.Count = 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]GetUserByUsername = "User not found"
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
GetUserByUsername = ex.ToString
[/SIZE][SIZE=2][COLOR=#0000ff]Finally
[/COLOR][/SIZE][SIZE=2]Conn.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Function
End Class[/COLOR][/SIZE]

Hope this helps.

Cheers!!

 

[hermandup]

O yes, this is the code for the connection string variable:

[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Const[/COLOR][/SIZE][SIZE=2] ConnectionString = "Persist Security Info=False;User ID=sa;Password=sa;Initial Catalog=WorkFlow;Data Source=196.33.215.6"

[/SIZE]

 

[TechGnome]

Never, ever, EVER use the sa account for data access. ALWAYS create your own login and use that. Believe it or not, the SA account is too vulnerable.

-tg