Cannot connect to SQL Servrer 2005 remotely using another account than sa

Hi there,
I set up an SQL Server 2005 Enterprise Edition, so it is listening on
the network.
I can login successfully from a remote machine using osql:
osql -H sqlhost -U sa
Password: XXX
1>
However I created another user "test" (SQL Auth.) and also a database
which test should be allowed to access but test cannot login remotely
(test _can_ login locally, for example in the management studio)
osql -H sqlhost -U test -d db_test
Password: XXX
Login failed for user 'test'.
What is strange about it, is that it doesn't even show up in the logs
that it did not work. There is no mention of the user "test" in the
logs when the remote login fails.
Any idea what to check? What am I doing wrong? :-(
Regards,
marcNote that you should use -S server_name\instance_name.
Perhaps you are connecting to a local instance.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Marc" wrote:
> Hi there,
> I set up an SQL Server 2005 Enterprise Edition, so it is listening on
> the network.
> I can login successfully from a remote machine using osql:
> osql -H sqlhost -U sa
> Password: XXX
> 1>
> However I created another user "test" (SQL Auth.) and also a database
> which test should be allowed to access but test cannot login remotely
> (test _can_ login locally, for example in the management studio)
> osql -H sqlhost -U test -d db_test
> Password: XXX
> Login failed for user 'test'.
> What is strange about it, is that it doesn't even show up in the logs
> that it did not work. There is no mention of the user "test" in the
> logs when the remote login fails.
> Any idea what to check? What am I doing wrong? :-(
> Regards,
> marc
>|||When you are using 'osql -H sqlhost -U sa' without -S you are connecting to
the default instance on the local computer. Try it and run this to verify
select @.@.servername
go
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ben Nevarez" wrote:
> Note that you should use -S server_name\instance_name.
> Perhaps you are connecting to a local instance.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Marc" wrote:
> > Hi there,
> > I set up an SQL Server 2005 Enterprise Edition, so it is listening on
> > the network.
> > I can login successfully from a remote machine using osql:
> >
> > osql -H sqlhost -U sa
> > Password: XXX
> > 1>
> >
> > However I created another user "test" (SQL Auth.) and also a database
> > which test should be allowed to access but test cannot login remotely
> > (test _can_ login locally, for example in the management studio)
> >
> > osql -H sqlhost -U test -d db_test
> > Password: XXX
> > Login failed for user 'test'.
> >
> > What is strange about it, is that it doesn't even show up in the logs
> > that it did not work. There is no mention of the user "test" in the
> > logs when the remote login fails.
> >
> > Any idea what to check? What am I doing wrong? :-(
> >
> > Regards,
> > marc
> >
> >|||Yes you are right! Thanks!!

Cannot connect to SQL Express Remotely

I have SQL Express April CTP (from VS Beta 2) installed on machineA, but I cannot connect to it from machineB using the command "sqlcmd -S machineA\sqlexpress -E". This command works just fine from machineA.

I have enabled the TCP/IP protocol for the SQLExpress instance through the configuration manager, and turned off the Windows Firewall on machineA.

Any ideas on what I'm doing wrong?

Hi Michael,

By default network protocols (TCP/IP, named pipe) are disabled for SQL Server Express Editions.

You can use SQL Server configuration manager tool to Enable the server protocols on which the instance of SQL Server will listen.

hope this is helpful...

thanks..
Rajesh|||Does this help:

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx|||Thanks Euan -- That did the trick!|||Hi,

I have the same problem, I cannot connect remotely to sql server express

I have done all the steps in that article http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

but still not working remotely although locally is ok

I have tried also on domain lan and on workgroup lan

On domain, on different client computers with different domain users I received different error messages:

...login failed for user. the user is not asociated with a trusted sql server conenction...
or
...When connecting to SQL Server 2005, this failure may be caused by the

fact that under the default settings SQL Server does not allow remote

connections ... error locating server..
or
...When connecting to SQL Server 2005, this failure may be caused by the

fact that under the default settings SQL Server does not allow remote

connections ... login failed...

I am connectiong to my_machine\SQLEXPRESS with windows authentication, same as on the server machine

I cannot figure out what should be the problem, any help would be apreciated.

Thank you for your time.
Corina

Cannot connect to SQL Express Remotely

I have SQL Express April CTP (from VS Beta 2) installed on machineA, but I cannot connect to it from machineB using the command "sqlcmd -S machineA\sqlexpress -E". This command works just fine from machineA.

I have enabled the TCP/IP protocol for the SQLExpress instance through the configuration manager, and turned off the Windows Firewall on machineA.

Any ideas on what I'm doing wrong?

Hi Michael,

By default network protocols (TCP/IP, named pipe) are disabled for SQL Server Express Editions.

You can use SQL Server configuration manager tool to Enable the server protocols on which the instance of SQL Server will listen.

hope this is helpful...

thanks..
Rajesh|||Does this help:

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx|||Thanks Euan -- That did the trick!|||Hi,

I have the same problem, I cannot connect remotely to sql server express

I have done all the steps in that article http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

but still not working remotely although locally is ok

I have tried also on domain lan and on workgroup lan

On domain, on different client computers with different domain users I received different error messages:

...login failed for user. the user is not asociated with a trusted sql server conenction...
or
...When connecting to SQL Server 2005, this failure may be caused by the

fact that under the default settings SQL Server does not allow remote

connections ... error locating server..
or
...When connecting to SQL Server 2005, this failure may be caused by the

fact that under the default settings SQL Server does not allow remote

connections ... login failed...

I am connectiong to my_machine\SQLEXPRESS with windows authentication, same as on the server machine

I cannot figure out what should be the problem, any help would be apreciated.

Thank you for your time.
Corina

Cannot connect to SQL Express on 2003 Machine from XP machine

Hi,
Can someone help me with a connection problem!
I am trying to connect from an XP SP2 machine remotely to a SQL Express
database on a 2003 server.
Everytime I try to connect via Visual Web Developer, i get the
following error:
"An error has occurred while establishing connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server)"
I have tried:
- In Surface Area Configuration for Services. . . Enabled "Local and
Remote connections", Checked "Using Both TCP/IP and Names Pipes"
- In SQL Server configuration manager, under 'Protocols for SQL
Express', gone to properties of TCP/IP and then IP Addresses. Here i
have put a 0 for TCP Dynamic Ports and 1433 for TCP Port.
- Enabled Remote Admin Connections, 1 in SQLExpress.
The connection string I am trying to use is: xx.xxx.xx.xx/SQLEXPRESS
All this and still no joy!
Can anyone help or provide more ideas on what I could try!!!
Thanks
HHave you checked the Windows XP Firewall to see if port 1433 (MSSQL) is
blocked?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<harrys@.gmail.com> wrote in message
news:1162418209.991142.71240@.i42g2000cwa.googlegroups.com...
> Hi,
> Can someone help me with a connection problem!
> I am trying to connect from an XP SP2 machine remotely to a SQL Express
> database on a 2003 server.
> Everytime I try to connect via Visual Web Developer, i get the
> following error:
> "An error has occurred while establishing connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections. (provider: Named Pipes Provider, error: 40 - Could not
> open a connection to SQL Server)"
>
> I have tried:
> - In Surface Area Configuration for Services. . . Enabled "Local and
> Remote connections", Checked "Using Both TCP/IP and Names Pipes"
> - In SQL Server configuration manager, under 'Protocols for SQL
> Express', gone to properties of TCP/IP and then IP Addresses. Here i
> have put a 0 for TCP Dynamic Ports and 1433 for TCP Port.
> - Enabled Remote Admin Connections, 1 in SQLExpress.
> The connection string I am trying to use is: xx.xxx.xx.xx/SQLEXPRESS
> All this and still no joy!
> Can anyone help or provide more ideas on what I could try!!!
> Thanks
> H
>|||Yep - Running Zone Alarm, so even switched it off completely to test.
But I have another 2000 server that I can connect to fine, so it's
definitely a problem on the 2003 server.
No matter what I do, it doesn't seem to want to open the port!
Any ideas anyone else?
Thanks
H
Arnie Rowland wrote:[vbcol=seagreen]
> Have you checked the Windows XP Firewall to see if port 1433 (MSSQL) is
> blocked?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> <harrys@.gmail.com> wrote in message
> news:1162418209.991142.71240@.i42g2000cwa.googlegroups.com...|||And is port 1434 (for the SQLBrowser service) also open.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<harrys@.gmail.com> wrote in message
news:1162467876.092108.66640@.m7g2000cwm.googlegroups.com...
> Yep - Running Zone Alarm, so even switched it off completely to test.
> But I have another 2000 server that I can connect to fine, so it's
> definitely a problem on the 2003 server.
> No matter what I do, it doesn't seem to want to open the port!
> Any ideas anyone else?
> Thanks
> H
>
> Arnie Rowland wrote:
>|||Yep it is. Even to be sure have opened UDP and TCP.
Probaly should have checked this before, but having just done a
'netstat -a' command, it seems that the 2003 server is not listening on
port 1433.
Any idea on what I can do to get it 'listening'?!
Thanks
H
Arnie Rowland wrote:[vbcol=seagreen]
> And is port 1434 (for the SQLBrowser service) also open.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> <harrys@.gmail.com> wrote in message
> news:1162467876.092108.66640@.m7g2000cwm.googlegroups.com...|||Lines: 1
X-Newsreader: Forte Agent 2.0/32.652
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: c-24-8-112-204.hsd1.co.comcast.net 24.8.112.204
Xref: leafnode.mcse.ms microsoft.public.sqlserver.connect:1062
Check the logs for the EXPRESS instance to see what port it
is listening on, what protocols, any issues with the
protocols and listening ports. It will log all that
information at start up.
You said you are using xx.xxx.xx.xx/SQLEXPRESS
for the connection string. That should be
xx.xxx.xx.xx\SQLEXPRESS
Make sure the browser service is actually running.
You will also want to make sure you can connect locally
before trying remotely.
-Sue
On 3 Nov 2006 03:00:49 -0800, harrys@.gmail.com wrote:
[vbcol=seagreen]
>Yep it is. Even to be sure have opened UDP and TCP.
>Probaly should have checked this before, but having just done a
>'netstat -a' command, it seems that the 2003 server is not listening on
>port 1433.
>Any idea on what I can do to get it 'listening'?!
>Thanks
>H
>
>Arnie Rowland wrote:|||Thanks for the checks Sue.
Looking at the log's it says:
Server is listening on [ 'any' <ipv4> 1433].
And this is on the Express instance.
But yet, it doesn't show up when doing the netstat -a command?
And i double checked the connection string, and i did have it the right
way round. Just a typo on here on my part!
Any other suggestions anyone?
Thanks
H
Sue Hoegemeier wrote:[vbcol=seagreen]
> Check the logs for the EXPRESS instance to see what port it
> is listening on, what protocols, any issues with the
> protocols and listening ports. It will log all that
> information at start up.
> You said you are using xx.xxx.xx.xx/SQLEXPRESS
> for the connection string. That should be
> xx.xxx.xx.xx\SQLEXPRESS
> Make sure the browser service is actually running.
> You will also want to make sure you can connect locally
> before trying remotely.
> -Sue
> On 3 Nov 2006 03:00:49 -0800, harrys@.gmail.com wrote:
>|||Actually, just going through the event viewer, i did find this entry:
The configuration of the AdminConnection\TCP protocol in the SQL
instance SQLEXPRESS is not valid.
Any one know what this means or how i fix it? Sounds like it could be
the source of my problems'
Thanks
H
Harry wrote:[vbcol=seagreen]
> Thanks for the checks Sue.
> Looking at the log's it says:
> Server is listening on [ 'any' <ipv4> 1433].
> And this is on the Express instance.
> But yet, it doesn't show up when doing the netstat -a command?
>
> And i double checked the connection string, and i did have it the right
> way round. Just a typo on here on my part!
> Any other suggestions anyone?
> Thanks
> H
>
> Sue Hoegemeier wrote:|||Is the event source SQLBrowser ?
That would be my guess. I'd look for SQLBrowser service
listening on UDP 1434 using netstat -abn, check for any
conflicts on 1434.
Make sure the Browser service is actually running, not just
started.
Do you have a default instance on this box? Are there any
conflicts on TCP 1434? Did you check the properties and
configurations for the TCP/IP protocol on the instance(s)?
Those are the areas you'd probably want to check.
-Sue
On 3 Nov 2006 10:21:23 -0800, "Harry" <harrys@.gmail.com>
wrote:
[vbcol=seagreen]
>Actually, just going through the event viewer, i did find this entry:
>The configuration of the AdminConnection\TCP protocol in the SQL
>instance SQLEXPRESS is not valid.
>Any one know what this means or how i fix it? Sounds like it could be
>the source of my problems'
>Thanks
>H
>
>Harry wrote:

Cannot connect to SQL Express on 2003 Machine from XP machine

Hi,
Can someone help me with a connection problem!
I am trying to connect from an XP SP2 machine remotely to a SQL Express
database on a 2003 server.
Everytime I try to connect via Visual Web Developer, i get the
following error:
"An error has occurred while establishing connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server)"
I have tried:
- In Surface Area Configuration for Services. . . Enabled "Local and
Remote connections", Checked "Using Both TCP/IP and Names Pipes"
- In SQL Server configuration manager, under 'Protocols for SQL
Express', gone to properties of TCP/IP and then IP Addresses. Here i
have put a 0 for TCP Dynamic Ports and 1433 for TCP Port.
- Enabled Remote Admin Connections, 1 in SQLExpress.
The connection string I am trying to use is: xx.xxx.xx.xx/SQLEXPRESS
All this and still no joy!
Can anyone help or provide more ideas on what I could try!!!
Thanks
H
Have you checked the Windows XP Firewall to see if port 1433 (MSSQL) is
blocked?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<harrys@.gmail.com> wrote in message
news:1162418209.991142.71240@.i42g2000cwa.googlegro ups.com...
> Hi,
> Can someone help me with a connection problem!
> I am trying to connect from an XP SP2 machine remotely to a SQL Express
> database on a 2003 server.
> Everytime I try to connect via Visual Web Developer, i get the
> following error:
> "An error has occurred while establishing connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections. (provider: Named Pipes Provider, error: 40 - Could not
> open a connection to SQL Server)"
>
> I have tried:
> - In Surface Area Configuration for Services. . . Enabled "Local and
> Remote connections", Checked "Using Both TCP/IP and Names Pipes"
> - In SQL Server configuration manager, under 'Protocols for SQL
> Express', gone to properties of TCP/IP and then IP Addresses. Here i
> have put a 0 for TCP Dynamic Ports and 1433 for TCP Port.
> - Enabled Remote Admin Connections, 1 in SQLExpress.
> The connection string I am trying to use is: xx.xxx.xx.xx/SQLEXPRESS
> All this and still no joy!
> Can anyone help or provide more ideas on what I could try!!!
> Thanks
> H
>
|||Yep - Running Zone Alarm, so even switched it off completely to test.
But I have another 2000 server that I can connect to fine, so it's
definitely a problem on the 2003 server.
No matter what I do, it doesn't seem to want to open the port!
Any ideas anyone else?
Thanks
H
Arnie Rowland wrote:[vbcol=seagreen]
> Have you checked the Windows XP Firewall to see if port 1433 (MSSQL) is
> blocked?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> <harrys@.gmail.com> wrote in message
> news:1162418209.991142.71240@.i42g2000cwa.googlegro ups.com...
|||And is port 1434 (for the SQLBrowser service) also open.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<harrys@.gmail.com> wrote in message
news:1162467876.092108.66640@.m7g2000cwm.googlegrou ps.com...
> Yep - Running Zone Alarm, so even switched it off completely to test.
> But I have another 2000 server that I can connect to fine, so it's
> definitely a problem on the 2003 server.
> No matter what I do, it doesn't seem to want to open the port!
> Any ideas anyone else?
> Thanks
> H
>
> Arnie Rowland wrote:
>
|||Yep it is. Even to be sure have opened UDP and TCP.
Probaly should have checked this before, but having just done a
'netstat -a' command, it seems that the 2003 server is not listening on
port 1433.
Any idea on what I can do to get it 'listening'?!
Thanks
H
Arnie Rowland wrote:[vbcol=seagreen]
> And is port 1434 (for the SQLBrowser service) also open.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> <harrys@.gmail.com> wrote in message
> news:1162467876.092108.66640@.m7g2000cwm.googlegrou ps.com...
|||Check the logs for the EXPRESS instance to see what port it
is listening on, what protocols, any issues with the
protocols and listening ports. It will log all that
information at start up.
You said you are using xx.xxx.xx.xx/SQLEXPRESS
for the connection string. That should be
xx.xxx.xx.xx\SQLEXPRESS
Make sure the browser service is actually running.
You will also want to make sure you can connect locally
before trying remotely.
-Sue
On 3 Nov 2006 03:00:49 -0800, harrys@.gmail.com wrote:
[vbcol=seagreen]
>Yep it is. Even to be sure have opened UDP and TCP.
>Probaly should have checked this before, but having just done a
>'netstat -a' command, it seems that the 2003 server is not listening on
>port 1433.
>Any idea on what I can do to get it 'listening'?!
>Thanks
>H
>
>Arnie Rowland wrote:
|||Thanks for the checks Sue.
Looking at the log's it says:
Server is listening on [ 'any' <ipv4> 1433].
And this is on the Express instance.
But yet, it doesn't show up when doing the netstat -a command?
And i double checked the connection string, and i did have it the right
way round. Just a typo on here on my part!
Any other suggestions anyone?
Thanks
H
Sue Hoegemeier wrote:[vbcol=seagreen]
> Check the logs for the EXPRESS instance to see what port it
> is listening on, what protocols, any issues with the
> protocols and listening ports. It will log all that
> information at start up.
> You said you are using xx.xxx.xx.xx/SQLEXPRESS
> for the connection string. That should be
> xx.xxx.xx.xx\SQLEXPRESS
> Make sure the browser service is actually running.
> You will also want to make sure you can connect locally
> before trying remotely.
> -Sue
> On 3 Nov 2006 03:00:49 -0800, harrys@.gmail.com wrote:
|||Actually, just going through the event viewer, i did find this entry:
The configuration of the AdminConnection\TCP protocol in the SQL
instance SQLEXPRESS is not valid.
Any one know what this means or how i fix it? Sounds like it could be
the source of my problems?
Thanks
H
Harry wrote:[vbcol=seagreen]
> Thanks for the checks Sue.
> Looking at the log's it says:
> Server is listening on [ 'any' <ipv4> 1433].
> And this is on the Express instance.
> But yet, it doesn't show up when doing the netstat -a command?
>
> And i double checked the connection string, and i did have it the right
> way round. Just a typo on here on my part!
> Any other suggestions anyone?
> Thanks
> H
>
> Sue Hoegemeier wrote:
|||Is the event source SQLBrowser ?
That would be my guess. I'd look for SQLBrowser service
listening on UDP 1434 using netstat -abn, check for any
conflicts on 1434.
Make sure the Browser service is actually running, not just
started.
Do you have a default instance on this box? Are there any
conflicts on TCP 1434? Did you check the properties and
configurations for the TCP/IP protocol on the instance(s)?
Those are the areas you'd probably want to check.
-Sue
On 3 Nov 2006 10:21:23 -0800, "Harry" <harrys@.gmail.com>
wrote:
[vbcol=seagreen]
>Actually, just going through the event viewer, i did find this entry:
>The configuration of the AdminConnection\TCP protocol in the SQL
>instance SQLEXPRESS is not valid.
>Any one know what this means or how i fix it? Sounds like it could be
>the source of my problems?
>Thanks
>H
>
>Harry wrote:

Cannot connect to SQL 2005 remotely from Vista

TITLE: Connect to Server

Cannot connect to iiii\dddbbb.

ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

What's blocking my remote connection to SQL 2005 on Windows 2003? I am using Vista Ulttima and the above message comes up after I clicked connect. However I have no problem to connect to same database from same location using XP. Can someone help?

Do you block port 1433 on your machine ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||I'm new to Vista. How can I tell if it's been blocked?|||

Anonymous wrote:

TITLE: Connect to Server

Cannot connect to iiii\dddbbb.

ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

What's blocking my remote connection to SQL 2005 on Windows 2003? I am using Vista Ulttima and the above message comes up after I clicked connect. However I have no problem to connect to same database from same location using XP. Can someone help?

In addition, I can't run/debug my .net web app from VS2005 on Vista where I have no problem using XP Pro to do the same thing. The message I got is same as above.

|||

Can moderator look into this issue... I can't see my original thread nor see any of my postings under my threads since I was prompted to change my display name. Forturnately I still keep copy of alert emails which allow me trace my threads.

Hi Jens,

The machine I'm using is a new one, do you mean the 1433 on server or on my local machine?

Cannot connect to SQL 2000 remotely

On the server, from the Start menu, point to All Programs, point to
Microsoft SQL Server, and then click Server Network Utility. That utility
should help you understand if SQL Server is using the IPX/SPX or TCP/IP
protocols, which might indicate a problem. On the client there is a similar
program, the Client Network Utility. Obviously the client and server should
be using the same protocols.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Clive" <clive.elsworth@.takeoutonetel.net> wrote in message
news:OxUCSSVyHHA.5856@.TK2MSFTNGP02.phx.gbl...
> I'm trying to install a bespoke application for a new client, and am
> embarassed by not being able to connect to their DB Server.
> The server and clients are XP all running on a workgroup called OFFICE
> File access is not problem and the DB Server name appears in a dropdown
> box of SQL servers on the network - e.g. when trying to create an ODBC
> DSN.
> The SQL Server is now configured to accept Windows and SQL authentication
> connection requests - and restarted.
> I have switched off or uninstalled everything that looks like a firewall
> on both server and clients.
> When trying to set up local ODBC DSN to connect as sa, I can connect no
> problem, but from one of the client laptops I just get the error:
> "Access denied or SQL Server does not exist"
> Can anyone tell me where else to look or what else to try?
> Is there a step by step procedure which could take me to the cause of the
> problem?
> The network is a wireless Lan - and IPX/SPX is featuring in the network
> stack. Could that be something to do with it?
> Any help very very much appreaciated!
> Thank you
> Clive (London UK)
>
>
>
>
Glad to help. I recognize customers stress and confusion as a problem that
we (Microsoft) often cause... and have an obligation to resolve.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Clive" <clive.elsworth@.takeoutonetel.net> wrote in message
news:O7J9R4pzHHA.3400@.TK2MSFTNGP03.phx.gbl...
> Apologies for flame throwing. Default of DisableNetworkProtocols WAS the
> problem, but in any case
> thank for taking the time and trouble to get back to me Rick.
> Clive Elsworth
>
>
> "Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
> news:1CAB65A1-509E-4338-8C33-65D98FDA7300@.microsoft.com...
>

Cannot connect to SQL 2000 remotely

I'm trying to install a bespoke application for a new client, and am
embarassed by not being able to connect to their DB Server.
The server and clients are XP all running on a workgroup called OFFICE
File access is not problem and the DB Server name appears in a dropdown box
of SQL servers on the network - e.g. when trying to create an ODBC DSN.
The SQL Server is now configured to accept Windows and SQL authentication
connection requests - and restarted.
I have switched off or uninstalled everything that looks like a firewall on
both server and clients.
When trying to set up local ODBC DSN to connect as sa, I can connect no
problem, but from one of the client laptops I just get the error:
"Access denied or SQL Server does not exist"
Can anyone tell me where else to look or what else to try?
Is there a step by step procedure which could take me to the cause of the
problem?
The network is a wireless Lan - and IPX/SPX is featuring in the network
stack. Could that be something to do with it?
Any help very very much appreaciated!
Thank you
Clive (London UK)On the server, from the Start menu, point to All Programs, point to
Microsoft SQL Server, and then click Server Network Utility. That utility
should help you understand if SQL Server is using the IPX/SPX or TCP/IP
protocols, which might indicate a problem. On the client there is a similar
program, the Client Network Utility. Obviously the client and server should
be using the same protocols.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Clive" <clive.elsworth@.takeoutonetel.net> wrote in message
news:OxUCSSVyHHA.5856@.TK2MSFTNGP02.phx.gbl...
> I'm trying to install a bespoke application for a new client, and am
> embarassed by not being able to connect to their DB Server.
> The server and clients are XP all running on a workgroup called OFFICE
> File access is not problem and the DB Server name appears in a dropdown
> box of SQL servers on the network - e.g. when trying to create an ODBC
> DSN.
> The SQL Server is now configured to accept Windows and SQL authentication
> connection requests - and restarted.
> I have switched off or uninstalled everything that looks like a firewall
> on both server and clients.
> When trying to set up local ODBC DSN to connect as sa, I can connect no
> problem, but from one of the client laptops I just get the error:
> "Access denied or SQL Server does not exist"
> Can anyone tell me where else to look or what else to try?
> Is there a step by step procedure which could take me to the cause of the
> problem?
> The network is a wireless Lan - and IPX/SPX is featuring in the network
> stack. Could that be something to do with it?
> Any help very very much appreaciated!
> Thank you
> Clive (London UK)
>
>
>
>|||Could it be because the default MSDE 2000 sp3a setup is to Disable network
protocols?
It seems that you can't rely on the setup utility to warn of this kind of
change between sp3 and sp3a. You just have to make sure you read all the
gumf every time - just in case they've decided to quietly cripple your
system this time.
"Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
news:1CAB65A1-509E-4338-8C33-65D98FDA7300@.microsoft.com...
> On the server, from the Start menu, point to All Programs, point to
> Microsoft SQL Server, and then click Server Network Utility. That utility
> should help you understand if SQL Server is using the IPX/SPX or TCP/IP
> protocols, which might indicate a problem. On the client there is a
> similar program, the Client Network Utility. Obviously the client and
> server should be using the same protocols.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Clive" <clive.elsworth@.takeoutonetel.net> wrote in message
> news:OxUCSSVyHHA.5856@.TK2MSFTNGP02.phx.gbl...
>|||Apologies for flame throwing. Default of DisableNetworkProtocols WAS the
problem, but in any case
thank for taking the time and trouble to get back to me Rick.
Clive Elsworth
"Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
news:1CAB65A1-509E-4338-8C33-65D98FDA7300@.microsoft.com...
> On the server, from the Start menu, point to All Programs, point to
> Microsoft SQL Server, and then click Server Network Utility. That utility
> should help you understand if SQL Server is using the IPX/SPX or TCP/IP
> protocols, which might indicate a problem. On the client there is a
> similar program, the Client Network Utility. Obviously the client and
> server should be using the same protocols.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Clive" <clive.elsworth@.takeoutonetel.net> wrote in message
> news:OxUCSSVyHHA.5856@.TK2MSFTNGP02.phx.gbl...
>|||Glad to help. I recognize customers stress and confusion as a problem that
we (Microsoft) often cause... and have an obligation to resolve.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Clive" <clive.elsworth@.takeoutonetel.net> wrote in message
news:O7J9R4pzHHA.3400@.TK2MSFTNGP03.phx.gbl...
> Apologies for flame throwing. Default of DisableNetworkProtocols WAS the
> problem, but in any case
> thank for taking the time and trouble to get back to me Rick.
> Clive Elsworth
>
>
> "Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
> news:1CAB65A1-509E-4338-8C33-65D98FDA7300@.microsoft.com...
>

Cannot connect to AS2005 remotely using SSMS or BIDS

I really need help to understand why I can't connect to SSAS from a remote machine running SSMS or BIDS. Following is my setup:

SQLServer 2005 on a development server
Server Name: SQLWIN-1
Named Instance: SQLWIN1

If

I am running management studio locally from the server machine I can

connect to SSAS fine. However, when running from a remote machine the

connection fails with the following errors:

TITLE: Connect to Server

Cannot connect to SQLWIN-1\SQLWIN1.

ADDITIONAL INFORMATION:
A

connection cannot be made to redirector. Ensure that 'SQL Browser'

service is running. (Microsoft.AnalysisServices.AdomdClient)

No connection could be made because the target machine actively refused it (System)

What I have already verified & tried:
SQL Browser service is running under NT Authority/Network Service account.
SSAS properties has local and remote connections radio button checked.
I even unabled anonymous login for SSAS on SQLWIN1

If I change the SQL Browser to run under the local system account, i get a slightly different error as follows:

TITLE: Connect to Server

Cannot connect to SQLWIN-1\SQLWIN1.

ADDITIONAL INFORMATION:
A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

Unable

to read data from the transport connection: An established connection

was aborted by the software in your host machine. (System)

An established connection was aborted by the software in your host machine (System)

When connecting from another machine I am not able to change the Windows Authentication username and I know the default username doesn't exist on the server.

I am kinda stuck and don't know what else I can try.

Any help is appreciated. Thanks

Hello. Check this. Under programs, SQL Server 2005 and configuration tools you will find SQL Server Surface Area Configuration.

Start this and check if remote connections are allowed for Analysis Services.

If this setting is correct you need to check if a firewall is activated on the server with SSAS 2005 or if the services packs are not the same on the server and your remote client.

HTH

Thomas Ivarsson

|||

http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

Also try running SQL Browser under domain account.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thomas,

I have run Surface Area configuration and have remote connections enabled for SSAS.

Regarding Service packs, I installed SQL Server 2005 SP1 on the server which is running Windows 2003 Server. My client machine is running XP professional. Are there any client patches that I need to install for SSMS?

Also, the server does not use domain authentication. We just have this one server which is windows and it has local user authentication.

Also, I notice that when connecting to SSAS from management studio, you don't have the option to change the authentication type or the username & password. They are greyed out. By default, I see a username in the dialog box that I know does not exist or is not going to be valid on the server. Is there a way to change this?

Thanks,
Arpan Shah|||

I do not think that you can change the autentication for SSAS 2005 in management studio. SSAS only support windows athentication. SQL Server both support SQL Server autentication and Windows autentication.

You will need to run SP1 on your client as well if you have BI-Dev Studio on that machine.

HTH

Thomas Ivarsson

|||Edward,

What account are you referring to as domain account? Ther server is not associated with any domain. It just has local administrator accounts.

The service is running under the NTAUTHORITY/Network Service account. I have tried running it under the Local System account as well. The service listens for DB connections and connects them properly. However, SSAS connections don't work remotely.

When i do a netstat on the server, I can see the ports associated with the Analysis server process id. I have a named instance of Analysis server, so its using a dynamic port. When I try to telnet to the server and analysis server dynamic port, it establishes a connection. So Network connectivity or firewall is not an issue.

However, when i try to use the server:port for SSAS in the SSMS connection dialog window it doesnt work. By default, the windows user account selected in the dialog box does not exist on the server. There is no way to change this so I have enabled anonymous connections as well. Still no luck..

Any help or insight is appreciated...

Thanks,
Arpan|||

make sure DB server has the same username/password in Windows that you used to logon to the remote machine.

Cannot connect remotely to SQLExpress via SQL Server Management Studio Express

Hi all,

I'm having a little trouble connecting to a SQLExpress instance on a remote domain using SQL Server Management Studio Express.

I've read that by default the program is set up locally only, so have edited the Surface Area Configuration. It only gives me the option to edit 'Services and Connections' on the localhost, but have edited the Remote Connections to use TCP/IP and Named Pipes.

In the Management Studio, I enter the server name, and login credentials. On the Connection Properties tab I changed the network protocol to TCP/IP though everytime I attempt to connect I get the following error message:

Any help would be greatly appreciated.

Rich

Hello,

Does this help you?http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Regards,

Louis

|||

Hmm, I followed the steps through, the only difference seemed the firewall exceptions which I have now edited, but still get the same error message.

Any ideas?

|||

from the client machine, can you ping the database server? Can you log the denial on the sql server box when you connect to it?

|||

How would I go about doing this?

Cheers

Cannot connect remotely to MSDE2000 database

Hi,
I have a win98 Pc connecting to a winxp pc - trying to connect to the
MSDE2000 database - the database is operating in mixed mode (allows SQL
logins) rather than windows integrated. The database is working ok on winxp
pc - i can use the SQL Web Data adminsitrator to verify that SQL Logins work
ok - also the ODBC Adminstrator can make connections to the database.
Now when i try using the ODBC Administrator to connect using the same
details it fails - SQL server does not exist or access denied.
Now i have disabled windows firewall to rule that out (although i had
enabled port 1433- sql server listening port).
Now how can i verify SQL server is listening on port 1433 for remote
connections?
How do i configre SQL server to listen for remote connections?
Any other ideas for resolving my connection problem?
Thanks a lot!
hi John,
John Blair wrote:
> Hi,
> I have a win98 Pc connecting to a winxp pc - trying to connect to the
> MSDE2000 database - the database is operating in mixed mode (allows
> SQL logins) rather than windows integrated. The database is working
> ok on winxp pc - i can use the SQL Web Data adminsitrator to verify
> that SQL Logins work ok - also the ODBC Adminstrator can make
> connections to the database.
> Now when i try using the ODBC Administrator to connect using the same
> details it fails - SQL server does not exist or access denied.
> Now i have disabled windows firewall to rule that out (although i had
> enabled port 1433- sql server listening port).
> Now how can i verify SQL server is listening on port 1433 for remote
> connections?
> How do i configre SQL server to listen for remote connections?
> Any other ideas for resolving my connection problem?
> Thanks a lot!
please verify, using Server Network Utility (svrnetcn.exe), that the MSDE
instance allows remote connections via the desired/required network
protocol...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks a lot - very immpressed - that did the trick - TCP/IP protocol was
disabled - i enabled it and it worked!
I've spent 2 days trying to get this working - eventually found out the
registry needed changing to allow mixed mode (SQL login)- found out my
firewall was blocking port 1433 - and finally your suggestion for enabling
the protocol - never even heard of that utility svrnetcn.exe - thanks very
much! John.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:37md0nF5eceloU1@.individual.net...
> hi John,
> John Blair wrote:
> please verify, using Server Network Utility (svrnetcn.exe), that the MSDE
> instance allows remote connections via the desired/required network
> protocol...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Exposing SQL through a firewall by opening port 1433 is generally considered
to be a bad thing.
-Andrew
"John Blair" <john_blair@.hotmail.com> wrote in message
news:H2oRd.910$gv6.611@.newsfe3-gui.ntli.net...
> Thanks a lot - very immpressed - that did the trick - TCP/IP protocol was
> disabled - i enabled it and it worked!
> I've spent 2 days trying to get this working - eventually found out the
> registry needed changing to allow mixed mode (SQL login)- found out my
> firewall was blocking port 1433 - and finally your suggestion for enabling
> the protocol - never even heard of that utility svrnetcn.exe - thanks very
> much! John.
>
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:37md0nF5eceloU1@.individual.net...
>
|||I am only exposing this port on a local intranet - so i wasn't aware that
there are any security risks. Do you have a better way of letting VB6 apps
access a remote SQL Server database? Thanks.
"Andrew" <nemoby@.nospam.nospam> wrote in message
news:uVC2%23ofFFHA.4016@.TK2MSFTNGP09.phx.gbl...
> Exposing SQL through a firewall by opening port 1433 is generally
> considered to be a bad thing.
> -Andrew
> "John Blair" <john_blair@.hotmail.com> wrote in message
> news:H2oRd.910$gv6.611@.newsfe3-gui.ntli.net...
>

Cannot connect remotely to MSDE2000

Hi
I uninstalled SQL7 from a machine and then installed
MSDE2000 (using setup disablenetworkprotocols=0
securitymode=sql sawpw=x)
I can connect localy without a problem but as soon as I
try and connect (via EM or even ODBC) remotely, I get a
network library error. I've tried connecting with TCP and
Named Pipes protocols.
Any ideas?
H
Hi,
As a first step, try to see if the tcp protocol really is enabled. Run
svrnetcn.exe on the MSDE box and make sure it is.
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:2ae5901c4685b$49dc6630$a601280a@.phx.gbl...
> Hi
> I uninstalled SQL7 from a machine and then installed
> MSDE2000 (using setup disablenetworkprotocols=0
> securitymode=sql sawpw=x)
> I can connect localy without a problem but as soon as I
> try and connect (via EM or even ODBC) remotely, I get a
> network library error. I've tried connecting with TCP and
> Named Pipes protocols.
> Any ideas?
> H

Cannot Connect Remotely to HTTP EndPoint

Dear all,

I appologise that incarnations of this topic have been posted several times on this forum, however I cannot find a scenario that is the same as my own so I've resorted to starting a new thread.

I can successfully create and connect remotely to an HTTP EndPoint on my Windows 2003 machine using Integrated security, the administrator username and password via my static IP address.

I have read that Windows XP SP2 supports HTTP EndPoints, and it would be much more convenient for me to be able to create EndPoints in XP, and connect remotely.

I have created the endpoint like so...

CREATE ENDPOINT sql_tvr10
STATE = STARTED
AS HTTP(
PATH = '/sql/tvr10', AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR))

FOR SOAP (
WEBMETHOD 'StoredProcedure1'
(name='master.dbo.StoredProcedure1'),
WSDL = DEFAULT,
BATCHES = ENABLED,
DATABASE = 'master',
NAMESPACE = 'http://194.106.11.11/' )

In visual studio I can retrive a list of services running at http://localhost/sql/tvr10?WSDL absolutely fine. However, in Visual Studio on the remote machine, I cannot connect to http://194.106.11.11/sql/tvr10?WSDL - it prompts me for a username and password, however I cannot select the username - it is set to "Athlon64\Guest" (Athlon64 is my server computer name). I have no passwords on any of my windows accounts (except administrator), and I have no database passwords set.

Could someone please tell me how I should go about accessing the HTTP EndPoint?

Just so it is clear, I am running a small LAN with no domain, just a workgroup. I need the client application to be able to connect via http regardless of which network it is on. I am using VS2005 and SQL Server 2005 Dev Ed.

Many thanks in advance,
Simon

Hi Simon,

On a workgroup network, I would recommend changing the "AUTHENTICATION" value on the endpoint from "INTEGRATED" to "NTLM". By default VS2005 will try Kerberos first when the server reports that it supports "INTEGRATED" authentication. I remember encountering issues where it will not retry with NTLM. By forcing NTLM on the SQL Server, will help elminate this problem. From a security perspective, I would recommend creating an user on the WinXP machine for use as the user who will access this HTTP Endpoint. You can then grant specific permissions in SQL (including endpoint connect permissions) for this user. This way, when prompted for credentials, you can specify this user "Athlon64\myUser".

Note: If VS2005 does not allow you to specify/choose the correct user, I suggest that you use Internet Explorer to retrieve the WSDL document. You can then save the WSDL document on your development machine and reference the file in VS2005.

A couple of things to remember,
- Ensure the WinXP firewall is not blocking TCP port 80
- Ensure that the WinXP IIS is not running (as it will take over port 80 and you will not end up connecting to SQL Server)

Jimmy

|||

Thanks for your advice, I think I had already tried a NTLM endpoint with no success. However I did as you said, and created a new windows user called db (and set a password),
then from the console I typed ...

"CREATE LOGIN [Athlon64\db] FROM WINDOWS; go",
"CREATE USER [Athlon64\db] FOR LOGIN [Athlon64\db]; go",
"grant connect on endpoint ::sql_tvr14 to [Athlon64\db]; go"

And tried searching http://194.106.11.11/sql_tvr14 for web services, and this time instead of prompting for the password of "Athlon64\GUEST", it prompted for the password of "Athlon64\db" - I thought 'BINGO', but alas when I type the password I specified in Windows control panel it just throws it back at me :'(. I'm pulling my hair out on this one!

I have disabled all my firewalls (router and windows), and I have stopped the ISSAdmin service in Control Panel -> Admininstrator -> Services.

If anyone can solve this I will owe them some sort of Wookie life debt!

|||

If you are in a workgroup environment using integrated logins will be problematic because there is no shared trust (no domain controller). One trick that might work is creating the same account on client and server machines with the same password (create db account on client machine with same pwd as account on Athlon64 machine), then log in as db user, run IE and hit the site, it should work with NTLM because the hash for both accounts will be the same.

Another thing you can try is use digest or basic, which work by hashing or sending the userid and password and then doing the authentication on the server side. Basic over SSL will definately work, I have not tried digest in this case but in theory it should work.

|||Thanks, I'll give that a go now.
Is it worth mentioning that the "Athlon64\db" user I created cannot even log on on the server computer using "osql -U [Athlon64\db]" from the command line? Is there a problem with the way I created a new login/user above?|||I have just tried your suggestions, including creating a new endpoint using BASIC or DIGEST, and I'm still having no luck. BASIC over SSL just refuses the connection (Error 400), DIGEST prompts for a UN and password, but rejects the input.

I am beginning to suspect there's a problem with the user accounts on the server. If I try "osql -U [Athlon64\db]" and the password I specified when I created the user account in control panel, should I be able to connect (because I can't!)? The only way I can connect to the db via the command line is "osql -E", and that logs me in without asking for any passwords.

:'(|||

osql -U [Athlon64\db] will not work.

Instead do this:

goto c:\windows\system32 in explorer, hold down shift key and right click cmd.exe, select Run As... and then enter userid and password for Athlon64\db.

Then from this command prompt (whcih is now running as Athlon64\db) run:

osql -E

This will attempt integrated login.

You can also test launching IE from this console window (run iexplore.exe) and then IE will be running as this user.

Also, inside IE make sure you goto Tools | Internet Options -> Advanced and check "Enable Integrated Windows Authenication" and restart IE if this is not checked.

|||

That worked, so now where do I go from here?

I'd also just like to thank everyone for their support; it's most kind of you guys to volunteer your time to a stranger!

|||

Not sure what your end result is, what are you trying to accomplish with the endpoint?

|||Basically I need to be able to query a personal database via http/the internet in C#, including use from a PDA.

As I said in my first post I had it working well with Server 2003, so I may have to go back to that. The only trouble being that my Server2003 drive crashed and I'll need to reinstall and configure everything :(|||

Yes if it's from a PDA I would use https and basic authentication, this would be the simplest and easiest thing to configure.

With basic the client sends the user id and password and this is authenticated on the server side using only the local security authority so you don't need a domain controller.

Basic should work well even from IE no matter what.