SQL 2008 from Visual Studio 2005 Express

I have created a database that I intend to maintain/update from a Visual Basic application that runs locally and pushes an updated set of records to different sites. The Winhost provided connection string for this DB is:

"Data Source=tcp:s01.Winhost.com;Initial Catalog=DB_xxx_mls;User ID=DB_xxx_mls_user;Password=xxx;Integrated Security=False;"

Though this may work from an ASP.NET web page (have not tried it), it does not work from within my Visual Basic 2005 Express application. I have tried several different connection strings that give me a variety of errors. I have tweaked it to the following that seems to get me the closest:

"Provider=SQLOLEDB;Server=tcp:s01.Winhost.com;Database=DB_xxx_mls;User ID=DB_xxx_mls_user;Password=xxx;Trusted_Connection=False;"

However, this is still giving me a login failed error.

I have tried to connect to the database using MS SQL Server Management Studio and with the Winhost server/database/user/password info provided and I also get a login error.

I am I brain-dead?? This should not be so difficult.

 

You can't connect to an SQL 2008 database using SQL Server Management Studio 2005, and I suspect that may also apply to Visual Basic 2005...

http://support.Winhost.com/KB/a699/how-to-migrate-from-your-local-sql-2005-database-to.aspx

 

Just for completeness in this post:

When connecting to a SQL Server 2008 database remotely using MS SQL Management Studio, you must click the Options button on the connection dialogue and manually type the name of the database to connect to and select TCP/IP as the Network Protocol. Otherwise a login error will occur.

My previous hosting provider did not require that information. Once logged in I could choos from a list of available DB's, only one of which I had permissions to view/edit.

 

Actually in SQL 2005, when you log in using SSMS you will see a list of DB's. That's by design by Microsoft. With SQL 2008, when you log in using SSMS you will only see your database. You will not see any one elses database.

 

I am at my wits end on this. I have VBasic 2008 installed and have tried using ADO and .Net framework to connect and in both instances I get a login failed error from the Winhost Sql Server. I also cannot create a system DSN that points to the Winhost SQL Server. It fails with the same login error.

ADO Sample Code:

Dim cnnSQLRemote As New ADODB.Connection
cnnSQLRemote.ConnectionString = _
"Provider=SQLOLEDB;Server=tcp:s01.Winhost.com;" & _
"Database=DB_589_mls;" & _
"User ID=" & DB_589_mls_user;" & _
"Password=" & xxxx;" & _
"Trusted_Connection=true;"
cnnSQLRemote.Open()

-----
Net Sample Code

Dim conn As New ServerConnection
conn.LoginSecure = False
conn.Login = "DB_589_mls_user"
conn.Password = "xxxx"
conn.DatabaseName = "DB_589_mls"
conn.NetworkProtocol = NetworkProtocol.TcpIp
conn.ServerInstance = "tcp:s01.Winhost.com"
conn.Connect()

Do you have any idea what I am missing?? Is you SQL Server on port 1433?? Is this server running a 64-bit OS?? Mixed mode security?? SSMS connects just fine, but I need to be able to connect via my code. What I have done in the past (other hosting companies) is create a System DSN pointing to the remote server and then use that DSN to make the connection and execute SQL statements.

Any help is appreciated.

Keith

 

Try setting "Trusted_Connection=False;". If you set trusted connection to true it will use windows authentication. If you set it to false it will use the SQL server authentication.

 

Logical, but it made no difference whatsoever. Same error.

 

The command:
sqlcmd -S s01.Winhost.com -U DB_589_mls_user -P xxxx -d DB_589_mls

The result:
Msg 18456, Level 14, State 1, Server S01, Line 1
Login failed for user 'DB_589_mls_user'.

I guess that means its not my code, but is usually still me. Do I have to port forward 1433 on my router?? I have never had to with earlier versions of SQL Server.

Thanks for all your help.

Keith

 

Have you tried changing the password for your SQL database as a test?

 

I don't think it's a problem with the connection or any blocked ports. You are obviously connecting fine, but just your login is not working. Are you sure you are entering the correct user?

Also, the sql user password is initially set to your site password when you created the database, but is not synced afterward. So if you changed your site password, your sql password remains the same.

 

I just changed my password and double-checked the userid and password as listed on the SQL 2008 management page in the control panel. Still couldn't log in. Then I took my dyslexia glasses off. I have been typing in the database and userid as DB_589 when it is DB_598. See the previous post where I said it would be me. I am always right. Even when I am wrong.

Thanks for your tolerance of my impaired vision and inability to type things correctly - repeatedly over several days.

Keith

 

No worries man, it happens to all of us at one time or another.

 

You can connect using SSMS 2008. Please see this post http://forum.Winhost.com/showthread.php?p=6176#post6176

What I don't know if the VB application will connect. Hope this helps resolve your VB connection issues

Microsoft is discontinung all downloads, service updates, and support for MS SQL 2005 all versions I think in June of this year, AS of now you can't download and install or purchase SQL 2005 from a Microsoft web site, Microsoft Partner, or Microsoft Service Provider. So all the help files on Winhost web site for SQL 2005 are not helpful any more.

 

We do not have any Knowledge Base articles specifically relating to SQL 2005. We never offered it as an option.

Most Microsoft products have a life cycle, and that life cycle does not end when the product isn't available for download to the general public anymore. SQL 2005 is supported through 2016.

Any host that wants to continue offering SQL 2005 can certainly still buy licenses from Microsoft. In fact there are still There are still hosts using SQL 2000 (extended support for that doesn't end until 2013).

 

Sorry I replyed to a post I fully didn't understand. After I made the reply I re-read the post. After understanding what the post really said. It was to late for me to delete my reply.

This forum default settings is 1/2 hour that a author can edit there posts. Might want to concider changing the default setting to a longer time limit that allow a author to edit there own posts.