Invalid Connection

Hey everyone, hope you all can help me with this problem.

We have a remotely hosted website, but we have a SQL server in our company (powers our instore portal). I have set up our router so that i can connect to remote desktop and sql server. I can connect to the remote desktop and i can connect to the sql server with query analyzer. On our local site i can set up a connection to look at sqlserver.underpargolfutah.org and it works fine, everything goes well.

Now here is the problem. On the hosted site i have set up the following

web.config >

<appSettings>

<addkey="sql_dsn"value="server=************.underpargolfutah.org;database=online;uid=sa;pwd=*******"/>

</appSettings>

default.aspx >

PublicClass shopDB
'publicly declare typical stuff for connections
Public connAs SqlConnection
Public cmdAs SqlCommand
Public readerAs SqlDataReader
Public dsnAsString = ConfigurationSettings.AppSettings("sql_dsn").ToString
PublicFunction getBaseSelectionsByType(ByVal type)
'declare the connection
conn =New SqlConnection(dsn)
'declare command
cmd =New SqlCommand("SELECT * FROM category", conn)
'open the connection
conn.Open()
'grab data
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Return reader
EndFunction

EndClass

This should all work but i get the following error

[SqlException: Invalid connection.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
v3.shopDB.shopDB.getBaseSelectionsByType(Object type) in shopDB.vb:21
v3.shop_default.Page_Load(Object sender, EventArgs e) in default.aspx.vb:32
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +742

Any ideas? i am stumped.

Thanks in advance
-Darren

Hi Darren!

But it is not tradition way. I think, when you are going to make access to your mashine database from web hosting, then you need to grant the permission for dbaccess aspnet accout from web hosting. Do you know name of webhosting aspnet user? If it is. then do next:

osql -E -S (local)\YourSQLserverName -d yourdatabasename -Q "sp_grantdbaccess'your_aspnet_webhostingaccount'"
osql -E -S (local)\YourSQLserverName -dyourdatabasename-Q "sp_addrolemember 'db_owner','your_aspnet_webhostingaccount'"

Sincerely, LukCAD