SQL Server 2005 connection from VB.NET 2003

[Andy Baker]

Our application is written in VB.NET 2003 and uses SQL Server 2000. We have
a customer who wants to upgrade SQL Server 2005, so I recently installed the
developer edition to check whether it worked with our application. I changed
the connection string on the PC application to point to the new instance of
SQL server, and to my surprise it worked and connected to 2005 without any
changes in code. However when I made the same changes on the application
that accesses SQL server from CE.NET devices, it didn't and I get the error
message 'SQL server does not exist or access denied'. The code that I am
using is:
Dim cn as SqlConnection
cn = New SqlConnection("Data Source=xxx.xxx.xxx.xxx; Initial
Catalog=Vanputer; User Id=****; Password=****")
cn.Open
where the IP address is the address of the PC where SQL Server 2005 is
installed, Vanputer is name of the database in SQL Server 2005, and User Id
and Password are set up as a user in SQL Server 2005. All I have done has
changed the IP address from the server where SQL Server 2000 is installed to
the one where 2005 is installed, backed up the database from 2000 and
restored to 2005. My questions are:
1) Is it possible to connect to SQL Server 2005 from VB.NET 2003 and
compact framework 1.1 or do I have to use VB.NET 2005?
2) If it is possible, what else am I doing wrong? Do I have to install
anything else on the PC where 2005 is installed to allow access from CE
devices, or can I change the connection string / use ODBC instead of
SqlConnection?
Thanks in advance

Andy Baker

 

[Ginny Caughey [MVP]]

Andy,

I haven't tried it, but it seems to me that it should work. Can you attach
to the SQL 2005 server using the IP address from another desktop computer? I
think you also need to speicfy the authentication mode in the connection
string as well as the user ID and password.

 

[Ron Weiner]

Here is the Function we use to connect our VS2003 VB.Net CF1.0 app to a
Varity of SqlExpress 2005 Databases. Can't imagine that it won't work with
it's big brother.

Public Function GetRemoteSqlDBConnectStr(ByVal strExtServerName As String,
ByVal strExtDbName As String) As String
' Purpose Returns the connect string for the remote Sql Database
Return "Server=" & strExtServerName & "; " _
& "Database=" & strExtDbName & "; Connect Timeout=10;" _
& "User ID=UserLogin; Password=PassWord;"
End Function

Been working for a couple years with named (not IP address) servers.

Typical Calling code"

Dim cn As New SqlConnection
cn.ConnectionString = GetRemoteSqlDBConnectStr("MyServer", "MyDatabase")
cn.Open()
' Do Stuff...
cn.Close
cn = Nothing

Ron W.

 

[Andy Baker]

Thanks for the reply. That's more or less what I'm doing, except with an IP
address instead of the server name. I have tried using the server name and
still get the same problem. What method of security are you using on the SQL
Server 2005 database? I have using integrated security for the network PC,
but have set up a user name and password for the CE device. This worked with
SQL Server 2000, but I'm wondering if this is the problem with 2005.

Andy Baker

 

[Ginny Caughey [MVP]]

Andy,

You can still use integrated security, but you have to provide the
domainName\userName and password in the connection string. Also make sure
you have tcp/ip enabled for your SQL 2005 server for accessing via ip
address.

 

[Andy Baker]

Thanks Ginny - I am getting there now. Tcp/ip was not enabled, and now I
have enabled it I can connect. Unfortunately, I am still not able to log in.
I now get the error "Login failed for user 'USERNAME' The user is not
associated with a trusted SQL server connection".
I am getting confused with the integrated security issue. When the
database was originally set up in SQL Server 2000, integrated security was
used to allow users to log on to the database when logged onto the windows
domain without having to supply an additional password. When the requirement
for CE devices was added, a username/password combination was set up in SQL
Server against the database, to be used for all CE devices, and this worked
OK in SQL Server 2000. All I have done is backup the database in 2000 and
restore it in 2005. When I look at the properties of the user in SQL 2000,
the following items are checked under Database Role Membership: public,
db_owner, db_datareader and db_datawriter. Looking at the same properties in
SQL 2005, there is not an option for public, so I thought that SQL server
doesn't know about the individual database users as it asks for a logon
before selecting a database. I then set up the same user under the instance
of 2005 rather than the database - if that make sense - unfortunately I
don't know enough about SQL Server to know that what I am doing is correct.
I still get the same problem if I connect from the CE device. I also tried
logging in using SQL server authentication and the username/password that I
set up for the CE device, from the SQL server 2005 main screen. I get the
message 'A connection was successfully established with the server, but then
an error occurred during the login process (provider: Shared Memory
Provider, error:0 - No process is on the other end of the pipe.)(Microsoft
SQL server, Error: 233).
At least is looks as if there is no problem with using SQL Server 2005
with VB.NET 2003, but it looks like the logon process is different between
2000 and 2005.

Andy Baker

 

[Ginny Caughey [MVP]]

Andy,

It sounds like you're not providing a valid domain name as part of the user
name. The syntax is DomainName\UserName with a backslash separating them.

 

[Andy Baker]

Hi Ginny

Found it - the Server authentication mode was set to Windows Authentication
mode! I changed this to SQL Server and Windows Authentication mode,
recreated the user and added Integrated Security=false to the connection
string and it more or less works. Completely my fault, and nothing to do
with the conversion from 2000, but at least it's working. I am still getting
a few problems - once I got an 'General Network Error' which I suspect is
due to the fact that SQL Server 2005 is running very slowly on an old PC and
is probably timing out somewhere. Thanks for your help.

Andy Baker

 

[coolsudh]

The prob could also be due to client protocol TCP/IP not being
enabled. Make the change in the configuration Manager. Do the same int
the services tab also. It worked for me