Saturday, February 25, 2012

Remote cube processing

Hi,

I'm using Informatica 8 for ETL procedures and I would like my SASS 2005 to process a cube as the ETL ends.
Does anyone know if there's a component for Informatica that supports XMLA (or any other way to process the cube remotly) ?

Thanks in advance,

Ariel.

Hi,

you can use a command line tool that is avaliable from microsoft,

it is called as ASCMD.

Check this link for download and other information on ASCMD. : http://msdn2.microsoft.com/en-us/library/ms365187.aspx

Hope this helps

Regards

|||

Hi Vijay,

I'll try it on my system and get back here (it's not connected to the internet).

Thanks a lot.

|||Hi all,

Well, I checked my sytem and my ETL server is linux so I can't use ascmd because it works only on windows servers.
Any workaround or suggestions?

Thanks in advance,
Ariel.|||

Hi,

In which database is the data stored after the ETL?

Regards

|||Hi,

The DB is Oracle.
Infronatica is installed on Linux server.|||

Hi,

This is what you can do:

Set up Http access to your Analysis Server 2005.

Link to setup http access: http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx

From your Linux system, which contains Informatica:

After your ETL completes,

pass a pre-constructed xmla command (Http call) ,

to the Http access point (msmdpump.dll) of the Analysis Server 2005.

This xmla command would contain a "Process" batch command,

which instructs the Analysis Server to process a specific object (Cube/MeasureGroup/Partition/dimension).

Infact you can even create new partitions and then process them dynamically using XMLA.

xmla overview :

http://msdn2.microsoft.com/en-us/library/ms187178.aspx

xmla command for processing analysis server 2005 objects:

http://msdn2.microsoft.com/en-us/library/ms187199.aspx

In Analysis Server 2005 you would have the data source configured to get data from Oracle or whichever data stores you have.

Hope this helps.

Regards

Remote creation of a Maintenance plan

I'm trying to remotely create a maintenance plan using the management studio in order to backup databases. I get the following error.

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

TITLE: Maintenance Plan Wizard Progress

Create maintenance plan failed.


ADDITIONAL INFORMATION:

Create failed for JobStep 'Subplan'. (Microsoft.SqlServer.MaintenancePlanTasks)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The specified '@.subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

The server doesn't have the Management studio client installed so I can't create the maintenance plan locally. If I install just the Management studio will the data currently in the server be effected? Any solution to the remote problem?

Thanks,

Neil

SSIS(SQL Server integration services) need to be installed along with the server, for executing maintenace plans successfully.

For SP1, the current plan is either SSIS or tools installed on the server box would address this issue.

Gops Dwarak

Remote creation of a Maintenance plan

I'm trying to remotely create a maintenance plan using the management studio in order to backup databases. I get the following error.

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

TITLE: Maintenance Plan Wizard Progress

Create maintenance plan failed.


ADDITIONAL INFORMATION:

Create failed for JobStep 'Subplan'. (Microsoft.SqlServer.MaintenancePlanTasks)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The specified '@.subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

The server doesn't have the Management studio client installed so I can't create the maintenance plan locally. If I install just the Management studio will the data currently in the server be effected? Any solution to the remote problem?

Thanks,

Neil

SSIS(SQL Server integration services) need to be installed along with the server, for executing maintenace plans successfully.

For SP1, the current plan is either SSIS or tools installed on the server box would address this issue.

Gops Dwarak

remote connnect MSSQL 2005 express

Can we remote connect MSSQL 2005 express with IP address?
If can how to done it?
With regards,
Goh
Basically:
1. Enable remote TCP connections.
2. Open the firewall if necessary.
3. Start SQL Server Browser Service if you don't know the port.
4.Specifiy the named instance of the SQL Server Express installation.
For details, start with the tutorial at
http://msdn2.microsoft.com/en-us/library/ms345318(en-US,SQL.90).aspx
Rick Byham
MCDBA, MCSE, MCSA
Lead Technical Writer,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Goh" <goh@.noemail.noemail> wrote in message
news:OECethpFGHA.644@.TK2MSFTNGP09.phx.gbl...
> Can we remote connect MSSQL 2005 express with IP address?
> If can how to done it?
> With regards,
> Goh
>
|||it work thanks.
"Rick Byham [MS]" <rickbyh@.online.microsoft.com> wrote in message
news:u9fd6FtFGHA.4036@.TK2MSFTNGP12.phx.gbl...
> Basically:
> 1. Enable remote TCP connections.
> 2. Open the firewall if necessary.
> 3. Start SQL Server Browser Service if you don't know the port.
> 4.Specifiy the named instance of the SQL Server Express installation.
> For details, start with the tutorial at
> http://msdn2.microsoft.com/en-us/library/ms345318(en-US,SQL.90).aspx
> --
> Rick Byham
> MCDBA, MCSE, MCSA
> Lead Technical Writer,
> Microsoft, SQL Server Books Online
> This posting is provided "as is" with
> no warranties, and confers no rights.
> "Goh" <goh@.noemail.noemail> wrote in message
> news:OECethpFGHA.644@.TK2MSFTNGP09.phx.gbl...
>

remote connnect MSSQL 2005 express

Can we remote connect MSSQL 2005 express with IP address?
If can how to done it?
With regards,
GohBasically:
1. Enable remote TCP connections.
2. Open the firewall if necessary.
3. Start SQL Server Browser Service if you don't know the port.
4.Specifiy the named instance of the SQL Server Express installation.
For details, start with the tutorial at
http://msdn2.microsoft.com/en-us/library/ms345318(en-US,SQL.90).aspx
--
Rick Byham
MCDBA, MCSE, MCSA
Lead Technical Writer,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Goh" <goh@.noemail.noemail> wrote in message
news:OECethpFGHA.644@.TK2MSFTNGP09.phx.gbl...
> Can we remote connect MSSQL 2005 express with IP address?
> If can how to done it?
> With regards,
> Goh
>|||it work thanks.
"Rick Byham [MS]" <rickbyh@.online.microsoft.com> wrote in message
news:u9fd6FtFGHA.4036@.TK2MSFTNGP12.phx.gbl...
> Basically:
> 1. Enable remote TCP connections.
> 2. Open the firewall if necessary.
> 3. Start SQL Server Browser Service if you don't know the port.
> 4.Specifiy the named instance of the SQL Server Express installation.
> For details, start with the tutorial at
> http://msdn2.microsoft.com/en-us/library/ms345318(en-US,SQL.90).aspx
> --
> Rick Byham
> MCDBA, MCSE, MCSA
> Lead Technical Writer,
> Microsoft, SQL Server Books Online
> This posting is provided "as is" with
> no warranties, and confers no rights.
> "Goh" <goh@.noemail.noemail> wrote in message
> news:OECethpFGHA.644@.TK2MSFTNGP09.phx.gbl...
>

Remote connecton

I made a 1 form app(to learn with) in VB Express. I published it, installed it on my pc and everything is fine(I used a db from my main app I'm building which was made in VBE(but I didnt install the db in my project, I only connected to it) anyhow

With my 1 form application, I can add,edit the records in the db, no problem there but

when I took it to another pc and installed it, it installed fine, but when you launch the app I got an exception stating

That it cannot find a SQL connection etc...

I do have remote connections checked in SQL.

Is there something that you have to do to a remote pc to make it visible?

We have a simple peer to peer network.

I have an existing app(Access) that I use mapped drives to share the data, I did open the folder for those mdb's and they were there which eliminates connectivity problems from pc to pc.

What did I do!!

Davids Learning

Fixed my problem!

Davids Learning

|||

Please post the solution you came to so that others can also learn.

|||

Learned from a remote pc that .\SQLServer doesnt work

Try the name of the pc and \SQLServer

Davids Learning

|||

That is corect. When you use the .\SQLExpress the . notion is a short form for the local machine.

BTW Thanks for posting the answer.

Remote Connectivity and Listening IP

I have been having a heck of a time connecting to a remote 2K5 instance from
my local Management Studio.
Each time I have tried to connect, regardless of the account, I have been
greeted with "SQL Server does not exist/access denied" errors.
I have configured the server to accept remote connections. It is configured
for mixed mode, and even my SA account cannot be authenticated. The error
logs dont even display my login attempts, which at first led me to believe
this was a firewall issue. The network admin has assured me that nothing at
the firewall level has changed, and I connected to the previous 2000
instance without a problem prior to this upgrade.
I went and checked the logs and noticed something that to me looks funny:
there are 4 entries in the log after startup that read:
- Server is listening on ['any' <ipv4 1358]
- Server local connection provider is ready to accept connection on [blah]
- Server local connection provider is ready to accept connection on [blah]
- Server is listening on [127.0.0.1 <ipv4> 1359]
Its the entry with the loopback that looks weird to me. Shouldnt this be the
server's IP?
Thanks
The IP address entries look normal but the port numbers are unusual. Not
wrong, just not the default setup. E.g. my server says:
Server is listening on [ 'any' <ipv4> 1433].
Server is listening on [ 127.0.0.1 <ipv4> 1434].
You can probably connect to <ip_address>, 1358
That is, the ip address and the port number.
You probably have a named instance. To connect to
<computer_name>\<instance_name> you need to start the SQL Server Browser
Service (which uses UDP port 1434) which will report the current port number
(1358) of your instance name back to your client so can connect to the
correct port.
You can start the the SQL Server Browser Service with the SQL Server
Configuration Manager.
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Elliot Rodriguez" <elliotrodriguezatgeemaildotcom> wrote in message
news:OSDEIi$MGHA.2628@.TK2MSFTNGP15.phx.gbl...
>I have been having a heck of a time connecting to a remote 2K5 instance
>from my local Management Studio.
> Each time I have tried to connect, regardless of the account, I have been
> greeted with "SQL Server does not exist/access denied" errors.
> I have configured the server to accept remote connections. It is
> configured for mixed mode, and even my SA account cannot be authenticated.
> The error logs dont even display my login attempts, which at first led me
> to believe this was a firewall issue. The network admin has assured me
> that nothing at the firewall level has changed, and I connected to the
> previous 2000 instance without a problem prior to this upgrade.
> I went and checked the logs and noticed something that to me looks funny:
> there are 4 entries in the log after startup that read:
> - Server is listening on ['any' <ipv4 1358]
> - Server local connection provider is ready to accept connection on [blah]
> - Server local connection provider is ready to accept connection on [blah]
> - Server is listening on [127.0.0.1 <ipv4> 1359]
> Its the entry with the loopback that looks weird to me. Shouldnt this be
> the server's IP?
> Thanks
>

Remote Connectivity and Listening IP

I have been having a heck of a time connecting to a remote 2K5 instance from
my local Management Studio.
Each time I have tried to connect, regardless of the account, I have been
greeted with "SQL Server does not exist/access denied" errors.
I have configured the server to accept remote connections. It is configured
for mixed mode, and even my SA account cannot be authenticated. The error
logs dont even display my login attempts, which at first led me to believe
this was a firewall issue. The network admin has assured me that nothing at
the firewall level has changed, and I connected to the previous 2000
instance without a problem prior to this upgrade.
I went and checked the logs and noticed something that to me looks funny:
there are 4 entries in the log after startup that read:
- Server is listening on ['any' <ipv4 1358]
- Server local connection provider is ready to accept connection on [bla
h]
- Server local connection provider is ready to accept connection on [bla
h]
- Server is listening on [127.0.0.1 <ipv4> 1359]
Its the entry with the loopback that looks weird to me. Shouldnt this be the
server's IP?
ThanksThe IP address entries look normal but the port numbers are unusual. Not
wrong, just not the default setup. E.g. my server says:
Server is listening on [ 'any' <ipv4> 1433].
Server is listening on [ 127.0.0.1 <ipv4> 1434].
You can probably connect to <ip_address>, 1358
That is, the ip address and the port number.
You probably have a named instance. To connect to
<computer_name>\<instance_name> you need to start the SQL Server Browser
Service (which uses UDP port 1434) which will report the current port number
(1358) of your instance name back to your client so can connect to the
correct port.
You can start the the SQL Server Browser Service with the SQL Server
Configuration Manager.
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Elliot Rodriguez" <elliotrodriguezatgeemaildotcom> wrote in message
news:OSDEIi$MGHA.2628@.TK2MSFTNGP15.phx.gbl...
>I have been having a heck of a time connecting to a remote 2K5 instance
>from my local Management Studio.
> Each time I have tried to connect, regardless of the account, I have been
> greeted with "SQL Server does not exist/access denied" errors.
> I have configured the server to accept remote connections. It is
> configured for mixed mode, and even my SA account cannot be authenticated.
> The error logs dont even display my login attempts, which at first led me
> to believe this was a firewall issue. The network admin has assured me
> that nothing at the firewall level has changed, and I connected to the
> previous 2000 instance without a problem prior to this upgrade.
> I went and checked the logs and noticed something that to me looks funny:
> there are 4 entries in the log after startup that read:
> - Server is listening on ['any' <ipv4 1358]
> - Server local connection provider is ready to accept connection on [b
lah]
> - Server local connection provider is ready to accept connection on [b
lah]
> - Server is listening on [127.0.0.1 <ipv4> 1359]
> Its the entry with the loopback that looks weird to me. Shouldnt this be
> the server's IP?
> Thanks
>

Remote Connections with SQL Developers Edition

Hi,

I want to allow someone to update the data in a database being developed in SQL Developers Edition. The idea is while I'm developing the database the person who's database it is can do the data entry. The data monkey lives no where near me so I cannot keep keep updating a local copy there...

I've tried to set up and connect but the server keeps refusing the connection. So question is... can SQL Developers Edition allow remote connections... and if so, which I hope it can, whats the way to go about setting it up.

Far as I can tell I'm ok in regards to specifying the location of the server, the SQL user account is set up and has access to the database.... can anyone help.


Yes dev edition supports remote connections, you need to go into the
surface area config tool and enable them however|||

Thank you Euan, thank you very much.

Can I also ask... can it work over a secured connection using TCP/IP and what would be involved in setting that up?

|||Whats your definition of secure? SQL Server supports SSL over the wire
is one way of securing it.|||Thats what I wanted to hear. Need to try find out to set it up so I may post again. Thanks.|||Try the security forum, they will be able to help.|||good idea thanks

Remote Connections using SQL Server 2005 Developers Enterprise Edition

I have a problem. I am using the developers version of SQL Server Enterprise edition.

I am trying to run a command in MS command prompt:

Aspnet_regsql -E -S localhost -ssadd -sstype p

After I execute this command, I receive a Name Pipe
error that under the default settings, SQL Server
doesn't allow remote connections. I took some steps
to try to resolve the problem:

1) I googled the interrnet to see if there was any one
else who ran into the same problem and if there was a
quick resolution.


2) I check SQL Server Books on line about SQL server
configuration manager and how to enable remote
connections using Name Pipes and TCP/IP.


3) I used C:\WINDOWS\system32\cliconfg.exe to enable
Name Pipes and TCPIP to be enable on the client.


4) I enabled SQl Server Browser to help me with my
problem


5) I stopped the Database engine, enable local and
remote connections using Name pipes and TCP/IP,then I
restarted the database engine along with SQl server
Agent.

6) I did check SQl Server error logs to see what port
it was listening on , but I thought that SQl Server
was suppose to listen on port 1433 by default, and
Name pipes /SQL/query.

7)I have check the error logs to see what port that SQL Server was listening to
and trying to use the port number in the client config utility. I am still getting the same error.

8) I tried to remove name pipes in the SQL Server Surface Manager and allow local and remote connections using only TCP/IP. I still get the same error. I did stop and restarted the Database Engine.

What steps have I not taken , and what should I do to correct this problem?

Hi,

have you refered below links?

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://support.microsoft.com/kb/914277/en-us

Hemantgiri S. Goswami

|||

Hi Hemantgiri,

Thank you for your reply.

I have reviewed the links that you recommended. I have completed most of the steps recommended. I am using a different firewall than Windows, and I will have to contact ZoneAlarm to receive more information about adding exceptions for SQL Server and SQL Server Browser. Usually, ZoneAlarm notify me to allow certain transactions to occur on my machine. I will ask the technical service people at ZoneAlarm why ZoneAlarm firewall was not informing me that it was blocking a remote connection to SQL Server from this machine. When I receive my requested information from ZoneAlarm and apply it to my firewall, I will let you know what happen next.

|||

Hi again,

I have taken the following steps to see if ZoneAlarm was presenting a problem. Please note that I have not heard from ZoneAlarm yet. I removed my wireless network card that I am using to conect to the internet. I disabled my ZoneAlarm and Windows firewall. I followed the steps in the article that was more focused on SQL Server Developer since I am not using SQL Express. I executed the command I mentioned earlier to see if I could connect to SQL Server remotely with out any firewall interference. I could not,and I received the same error. I did look at the other article that was more SQLExpress focused, but I am not sure how I would apply some commands in that article to SQL Server Developer. I have used the SQL Server Surface Configration to enable local and remote settings and using TCP/IP and Name Pipes. It shows that the radio buttons for both are selected, so I know that those choices are selected. As far as selecting a port, I am asking you what would be the SQLCMD command version for SQL Developer? The one for SQL Express is stated in the article, is there a similar one that I can use for SQL Developer? I tried to set the port using clientconfig.exe but that doesn't seem to work.

|||

Hi,

AFAIK their is no difference for the editions , you can perform the same steps.

Hemantgiri S. Goswami

|||

Hi everyone,

I am still looking into this situation. I do not know what to run the SQLCMD for SQL Developer. One article has it for SQL Express : SQLCMD -E -S YourServer\SQLEXPRESS,port #. My question is would I enter SQLCMD -E -S myserver\SQLDEVELOPER,port# or is there another way to state this?

|||

Hi everyone,

I have uninstalled the version of SQL Server 2005 and used an advanced version of SQL Express 2005. I have been successful in configuring remote connections and enabling Named Pipes and TCP/IP using this version. Even though I was not told by the previous version of SQL server 2005, I think that I did not correctly add myself to the Admin group to have the rights to change from local to local /remote connections and enable Named Pipes and TCP/IP. I will test my theory when I install my Standard edition of SQL Server 2005 during May or June.

I would like to thank everyone who replied to my post or who thought hard on a solution to my problem, but have not replied at this time.

Remote Connections using SQL Server 2005 Developers Enterprise Edition

I have a problem. I am using the developers version of SQL Server Enterprise edition.

I am trying to run a command in MS command prompt:

Aspnet_regsql -E -S localhost -ssadd -sstype p

After I execute this command, I receive a Name Pipe
error that under the default settings, SQL Server
doesn't allow remote connections. I took some steps
to try to resolve the problem:

1) I googled the interrnet to see if there was any one
else who ran into the same problem and if there was a
quick resolution.


2) I check SQL Server Books on line about SQL server
configuration manager and how to enable remote
connections using Name Pipes and TCP/IP.


3) I used C:\WINDOWS\system32\cliconfg.exe to enable
Name Pipes and TCPIP to be enable on the client.


4) I enabled SQl Server Browser to help me with my
problem


5) I stopped the Database engine, enable local and
remote connections using Name pipes and TCP/IP,then I
restarted the database engine along with SQl server
Agent.

6) I did check SQl Server error logs to see what port
it was listening on , but I thought that SQl Server
was suppose to listen on port 1433 by default, and
Name pipes /SQL/query.

7)I have check the error logs to see what port that SQL Server was listening to
and trying to use the port number in the client config utility. I am still getting the same error.

8) I tried to remove name pipes in the SQL Server Surface Manager and allow local and remote connections using only TCP/IP. I still get the same error. I did stop and restarted the Database Engine.

What steps have I not taken , and what should I do to correct this problem?

Hi,

have you refered below links?

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://support.microsoft.com/kb/914277/en-us

Hemantgiri S. Goswami

|||

Hi Hemantgiri,

Thank you for your reply.

I have reviewed the links that you recommended. I have completed most of the steps recommended. I am using a different firewall than Windows, and I will have to contact ZoneAlarm to receive more information about adding exceptions for SQL Server and SQL Server Browser. Usually, ZoneAlarm notify me to allow certain transactions to occur on my machine. I will ask the technical service people at ZoneAlarm why ZoneAlarm firewall was not informing me that it was blocking a remote connection to SQL Server from this machine. When I receive my requested information from ZoneAlarm and apply it to my firewall, I will let you know what happen next.

|||

Hi again,

I have taken the following steps to see if ZoneAlarm was presenting a problem. Please note that I have not heard from ZoneAlarm yet. I removed my wireless network card that I am using to conect to the internet. I disabled my ZoneAlarm and Windows firewall. I followed the steps in the article that was more focused on SQL Server Developer since I am not using SQL Express. I executed the command I mentioned earlier to see if I could connect to SQL Server remotely with out any firewall interference. I could not,and I received the same error. I did look at the other article that was more SQLExpress focused, but I am not sure how I would apply some commands in that article to SQL Server Developer. I have used the SQL Server Surface Configration to enable local and remote settings and using TCP/IP and Name Pipes. It shows that the radio buttons for both are selected, so I know that those choices are selected. As far as selecting a port, I am asking you what would be the SQLCMD command version for SQL Developer? The one for SQL Express is stated in the article, is there a similar one that I can use for SQL Developer? I tried to set the port using clientconfig.exe but that doesn't seem to work.

|||

Hi,

AFAIK their is no difference for the editions , you can perform the same steps.

Hemantgiri S. Goswami

|||

Hi everyone,

I am still looking into this situation. I do not know what to run the SQLCMD for SQL Developer. One article has it for SQL Express : SQLCMD -E -S YourServer\SQLEXPRESS,port #. My question is would I enter SQLCMD -E -S myserver\SQLDEVELOPER,port# or is there another way to state this?

|||

Hi everyone,

I have uninstalled the version of SQL Server 2005 and used an advanced version of SQL Express 2005. I have been successful in configuring remote connections and enabling Named Pipes and TCP/IP using this version. Even though I was not told by the previous version of SQL server 2005, I think that I did not correctly add myself to the Admin group to have the rights to change from local to local /remote connections and enable Named Pipes and TCP/IP. I will test my theory when I install my Standard edition of SQL Server 2005 during May or June.

I would like to thank everyone who replied to my post or who thought hard on a solution to my problem, but have not replied at this time.

Remote connections to named instances fails on SQL 2005 cluster

I have a new SQL Server 2005 cluster with multiple named instances of SQL
Server 2005 running. From any cluster machine, I can connect to any instanc
e
of SQL Server running in the cluster. From remote machines, I can connect t
o
the default instance of SQL Server, but I can not connect to any named
instances. Help?
+ SQL Browser is running on one physical machine in the cluster
+ Surface Area Configuration has "remote clients" enabled for all instances
+ Named pipes and TCP/IP are enabled
+ DTC is running on the cluster. It is configured to allow network access
on each physical machine.
+ Windows firewall is disabled. Remote machines on same subnet as cluster
machines.
+ Client machine can ping virtual servers?
After waiting a few hours, remote connections now work
?
"Bill Q" wrote:

> I have a new SQL Server 2005 cluster with multiple named instances of SQL
> Server 2005 running. From any cluster machine, I can connect to any insta
nce
> of SQL Server running in the cluster. From remote machines, I can connect
to
> the default instance of SQL Server, but I can not connect to any named
> instances. Help?
> + SQL Browser is running on one physical machine in the cluster
> + Surface Area Configuration has "remote clients" enabled for all instance
s
> + Named pipes and TCP/IP are enabled
> + DTC is running on the cluster. It is configured to allow network access
> on each physical machine.
> + Windows firewall is disabled. Remote machines on same subnet as cluster
> machines.
> + Client machine can ping virtual servers

Remote Connections Refused when server under load

Hi All,
We have a .NET 2.0 web application. Occasionally, we get the following
error below when we are doing performance testing. It is not consistent and
only happens every once in awhile. We are using TCP/IP and only have one
instance of SQL running. We do specify connection pooling in the connection
string. There is virtually nothing happening on the database server - it
only contains a few rows of data to control navigation for the site.
System.Data.SqlClient.SqlException: 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: Named Pipes Provider, error: 40 - Could
not open a connection to SQL Server
This is a different error and not quite what you describe but it might be
involved in your problem. This is from the Books Online topic "Server
Network Configuration."
Connections May Be Forcibly Closed When Running on Windows Server 2003 SP1
When testing scalability with a large number of client connection attempts
to an instance of the SQL Server Database Engine running on Windows Server
2003 Service Pack 1, Windows may drop connections if the requests arrive
faster than SQL Server can service them. This is a security feature of
Windows Server 2003 Service Pack 1, which implements a finite queue for
incoming TCP connection requests. It results in the following error:
ProviderNum: 7, Error: 10054, ErrorMessage: "TCP Provider: An existing
connection was forcibly closed by the remote host ...
To resolve this issue, use the regedit.exe utility to add the following
registry key:
Key Type Name Value
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\Tcpip\Parameters\
DWORD
SynAttackProtect
00000000
Security Note:
Setting this registry key may expose the server to a SYN flood,
denial-of-service attack. Add this registry value only if necessary and with
an understanding of the security risks. Remove this registry value when
testing is complete.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bill P" <Bill P@.discussions.microsoft.com> wrote in message
news:2D3D349D-EE1C-4945-82FE-D3717AD02E2F@.microsoft.com...
> Hi All,
> We have a .NET 2.0 web application. Occasionally, we get the following
> error below when we are doing performance testing. It is not consistent
> and
> only happens every once in awhile. We are using TCP/IP and only have one
> instance of SQL running. We do specify connection pooling in the
> connection
> string. There is virtually nothing happening on the database server - it
> only contains a few rows of data to control navigation for the site.
> System.Data.SqlClient.SqlException: 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: Named Pipes Provider, error: 40 -
> Could
> not open a connection to SQL Server
|||Rick,
Thank you for the reply. It very well could have something to do with this.
I am going to give it a try. It may be a few days before I find anything
out since this happens only once in a while.
Bill Portman
"Rick Byham, (MSFT)" wrote:

> This is a different error and not quite what you describe but it might be
> involved in your problem. This is from the Books Online topic "Server
> Network Configuration."
> Connections May Be Forcibly Closed When Running on Windows Server 2003 SP1
> When testing scalability with a large number of client connection attempts
> to an instance of the SQL Server Database Engine running on Windows Server
> 2003 Service Pack 1, Windows may drop connections if the requests arrive
> faster than SQL Server can service them. This is a security feature of
> Windows Server 2003 Service Pack 1, which implements a finite queue for
> incoming TCP connection requests. It results in the following error:
> ProviderNum: 7, Error: 10054, ErrorMessage: "TCP Provider: An existing
> connection was forcibly closed by the remote host ...
> To resolve this issue, use the regedit.exe utility to add the following
> registry key:
> Key Type Name Value
> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\Tcpip\Parameters\
> DWORD
> SynAttackProtect
> 00000000
> Security Note:
> Setting this registry key may expose the server to a SYN flood,
> denial-of-service attack. Add this registry value only if necessary and with
> an understanding of the security risks. Remove this registry value when
> testing is complete.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Bill P" <Bill P@.discussions.microsoft.com> wrote in message
> news:2D3D349D-EE1C-4945-82FE-D3717AD02E2F@.microsoft.com...
>

Remote Connections Refused when server under load

Hi All,
We have a .NET 2.0 web application. Occasionally, we get the following
error below when we are doing performance testing. It is not consistent and
only happens every once in awhile. We are using TCP/IP and only have one
instance of SQL running. We do specify connection pooling in the connection
string. There is virtually nothing happening on the database server - it
only contains a few rows of data to control navigation for the site.
System.Data.SqlClient.SqlException: An error has occurred while establishing
a connection to the server. When connecting to SQL Server 2005, this failur
e
may be caused by the fact that under the default settings SQL Server does no
t
allow remote connections. (provider: Named Pipes Provider, error: 40 - Could
not open a connection to SQL ServerThis is a different error and not quite what you describe but it might be
involved in your problem. This is from the Books Online topic "Server
Network Configuration."
Connections May Be Forcibly Closed When Running on Windows Server 2003 SP1
When testing scalability with a large number of client connection attempts
to an instance of the SQL Server Database Engine running on Windows Server
2003 Service Pack 1, Windows may drop connections if the requests arrive
faster than SQL Server can service them. This is a security feature of
Windows Server 2003 Service Pack 1, which implements a finite queue for
incoming TCP connection requests. It results in the following error:
ProviderNum: 7, Error: 10054, ErrorMessage: "TCP Provider: An existing
connection was forcibly closed by the remote host ...
To resolve this issue, use the regedit.exe utility to add the following
registry key:
Key Type Name Value
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl
Set\Services\Tcpip\Parameters\
DWORD
SynAttackProtect
00000000
Security Note:
Setting this registry key may expose the server to a SYN flood,
denial-of-service attack. Add this registry value only if necessary and with
an understanding of the security risks. Remove this registry value when
testing is complete.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bill P" <Bill P@.discussions.microsoft.com> wrote in message
news:2D3D349D-EE1C-4945-82FE-D3717AD02E2F@.microsoft.com...
> Hi All,
> We have a .NET 2.0 web application. Occasionally, we get the following
> error below when we are doing performance testing. It is not consistent
> and
> only happens every once in awhile. We are using TCP/IP and only have one
> instance of SQL running. We do specify connection pooling in the
> connection
> string. There is virtually nothing happening on the database server - it
> only contains a few rows of data to control navigation for the site.
> System.Data.SqlClient.SqlException: 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: Named Pipes Provider, error: 40 -
> Could
> not open a connection to SQL Server|||Rick,
Thank you for the reply. It very well could have something to do with this.
I am going to give it a try. It may be a few days before I find anything
out since this happens only once in a while.
Bill Portman
"Rick Byham, (MSFT)" wrote:

> This is a different error and not quite what you describe but it might be
> involved in your problem. This is from the Books Online topic "Server
> Network Configuration."
> Connections May Be Forcibly Closed When Running on Windows Server 2003 SP1
> When testing scalability with a large number of client connection attempts
> to an instance of the SQL Server Database Engine running on Windows Server
> 2003 Service Pack 1, Windows may drop connections if the requests arrive
> faster than SQL Server can service them. This is a security feature of
> Windows Server 2003 Service Pack 1, which implements a finite queue for
> incoming TCP connection requests. It results in the following error:
> ProviderNum: 7, Error: 10054, ErrorMessage: "TCP Provider: An existing
> connection was forcibly closed by the remote host ...
> To resolve this issue, use the regedit.exe utility to add the following
> registry key:
> Key Type Name Value
> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl
Set\Services\Tcpip\Parameters\
> DWORD
> SynAttackProtect
> 00000000
> Security Note:
> Setting this registry key may expose the server to a SYN flood,
> denial-of-service attack. Add this registry value only if necessary and wi
th
> an understanding of the security risks. Remove this registry value when
> testing is complete.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Bill P" <Bill P@.discussions.microsoft.com> wrote in message
> news:2D3D349D-EE1C-4945-82FE-D3717AD02E2F@.microsoft.com...
>

Remote Connections in 2005

This has probably been answered before, but...
I have a local SQL Server 2005 instance running, and I would like to
have another user on my network connect to this instance. However,
when he tries to connect, he always receives the same error message:
"An error has occured 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 28: - Server
doesnt' support requested protocol)"
So, I checked the properties for the server, and under the connections
tab the allow remote connection box is checked.
Any clue where to look next?
Thanks In Advancechris.teter@.gmail.com wrote:
> This has probably been answered before, but...
> I have a local SQL Server 2005 instance running, and I would like to
> have another user on my network connect to this instance. However,
> when he tries to connect, he always receives the same error message:
> "An error has occured 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 28: - Server
> doesnt' support requested protocol)"
> So, I checked the properties for the server, and under the connections
> tab the allow remote connection box is checked.
> Any clue where to look next?
> Thanks In Advance
>
is tcp and/or named pipes enabled - they are disabled by default

Remote Connections in 2005

This has probably been answered before, but...
I have a local SQL Server 2005 instance running, and I would like to
have another user on my network connect to this instance. However,
when he tries to connect, he always receives the same error message:
"An error has occured 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 28: - Server
doesnt' support requested protocol)"
So, I checked the properties for the server, and under the connections
tab the allow remote connection box is checked.
Any clue where to look next?
Thanks In Advancechris.teter@.gmail.com wrote:
> This has probably been answered before, but...
> I have a local SQL Server 2005 instance running, and I would like to
> have another user on my network connect to this instance. However,
> when he tries to connect, he always receives the same error message:
> "An error has occured 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 28: - Server
> doesnt' support requested protocol)"
> So, I checked the properties for the server, and under the connections
> tab the allow remote connection box is checked.
> Any clue where to look next?
> Thanks In Advance
>
is tcp and/or named pipes enabled - they are disabled by default

Remote Connections in 2005

This has probably been answered before, but...
I have a local SQL Server 2005 instance running, and I would like to
have another user on my network connect to this instance. However,
when he tries to connect, he always receives the same error message:
"An error has occured 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 28: - Server
doesnt' support requested protocol)"
So, I checked the properties for the server, and under the connections
tab the allow remote connection box is checked.
Any clue where to look next?
Thanks In Advance
chris.teter@.gmail.com wrote:
> This has probably been answered before, but...
> I have a local SQL Server 2005 instance running, and I would like to
> have another user on my network connect to this instance. However,
> when he tries to connect, he always receives the same error message:
> "An error has occured 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 28: - Server
> doesnt' support requested protocol)"
> So, I checked the properties for the server, and under the connections
> tab the allow remote connection box is checked.
> Any clue where to look next?
> Thanks In Advance
>
is tcp and/or named pipes enabled - they are disabled by default

Remote connections error SQL Server 2005

I am getting "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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) " when I access my application
But sql server is found to be running
what may be the reason
can any one guide me on thisEverything you could possibly want to know on connectivity issues:

http://blogs.msdn.com/sql_protocols.../22/506607.aspx

Brett
Perfect Computing, Inc

Remote Connections error

While trying to transfer my website project to another computer I am encountering an error when tryng to run the solution. It gives me a "Does not allow remote connections". Why is this when on the original computer everything worked fine.

Walter

Does the error has anything to do with SQL server?

This error is common when there is a connection problem to SQL server.

Verify your connection string.Make sure SQL server is running. Make sure you are able to connect to the database.

Regards,

David

|||

I think I had an error similar to this before and therefore I'll assume that your problem is like mine and is associated with SQL Server. (I thinkneutrino is of the same persuasion.)

Now if I understand you correctly, you're trying to run a project on one pc and connect to a database on another pc. Therefore I think the problem is that SQL Server is rejecting your remote connection because it's not configured to accept it! You need to allow remote connections to that server.

See this article (How to configure SQL Server 2005 to allow remote connections ).

Now.. if you're problem isn't related to SQL Server ... well.. Ignore all of the above. Big Smile

Remote Connections Enabled, but I still get error that Remote is not configured - Sql 2005

Fellow Devs,

I have an instance of SQL Server Express 2005 running on another box and I have Remote Connections enabled over both TCP/IP and Named Pipes, but on my other box I keep getting the error that the server does not accept Remote Connections.

Any ideas why this might be happening? Is there some other configuration?

start server configuration manager

click on protocols > TCP/IP and properties

go to IP addresses and under IPALL remove everythink from TCP dynamic port , under TCP Port enter port you want to use like 1433

save all changes restart service and should work

|||and if you have windows firewall on you have to allow to accept connection on this port|||

I don't see that option in my server configuration manager. I just see "File Server", "Application Server" and "Remote Access/VPN Server". Where do I modify thse settings?

Thanks!

|||

1) open SQL Server configuration manager
2) on the left under "Sql server 2005 network configuration" click on protocols for SQL
3) on right side right click TCP/IP > properties and tab IP addresses

|||

I was having the same problem and I followed your instructions, but now VS doesn't even detect the sql server. Any solutions?

Thanks.

|||

Swackhammer1:

I was having the same problem and I followed your instructions, but now VS doesn't even detect the sql server. Any solutions?

Thanks.

If VS is not detecting the SQL Server it may mean your SQL Server service maybe off. You may want to download the advanced version from the link below if not first get the eval and then spend $33 to get the developer edition. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx

|||

looool what kind of solution is that ? doesn't work pay $33.

here is an article explaining step by step what you can do to enable remote connections - pretty much same idea i gave you , but when you will follow it must work

|||You have given the original poster very low level usually not adviced connection to SQL Server and it is not working, Express is best used for small company intranet hosted application nothing more. I have used SQL Server since 1998 and I have not got the need to connect to SQL Server on those layers. So mine will cost after 180 days but it is pain free.|||

i saw many of your posts and i KNOW that you work with SQL for long time and you know about it much more than i do.
I agree that SQL express is perfect for small project, intranet & for development and i believe that is what this person needs - when someone ask how to enable remote connections in SQL express- my guess would be that he/she doesn't work for BIG corporation that has billions of transaction / day

My point is that there is no reason to buy anything cos EXPRESS edition can work perfectly all you need is to spend few minutes with it and make the setup + maybe change firewall settings.

|||Yes but connecting to SQL Server through TCP port is not good advice because there is also the known UDP port and two others Microsoft admit to have reserved but only give to customers as needed which opens you up to known security issues. Microsoft was like Oracle selling the developer edition for hundreds we asked for the lower price for access and got it. Fighting with Express eats into development time.|||Ok. Here's the thing. I'm using SQL EXPRESS and VS 2005 on my machine, but the company I'm developing for has the full SQL SERVER 2005. Before I changed the tcp/ip setting, I could see my server in VS. After changing the settings though, it no longer shows up. I do have the service running. I double checked.|||OK. I decided to get the trial version of SQL SERVER 2005 for now. Let's see how that works out.|||

The links below from Microsoft covers most of what you need and some of what I have been trying to explain in details. Hope this helps.

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

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

|||

Caddre - very good links :)

BTWSwackhammer1 where in VS you can not see your SQL server ? in databse explorer when you click "add connection" ? if that is the problem just enter ".\SQLEXPRESS" in server name field or IP address of you server or "YOUR_COMPUTER_NEME\SQLEXPRESS"

Remote Connections

Hi,

I'm having problems setting up remote connections in SQL Server 2005 Dev. I can't see what I've done wrong maybe some else can. I'm trying to connect to the database through a .NET 2005 application which uses table adapters, the software works fine when using trusted connections.

I've turned on TCP/IP remote connections and I restarted the SQL Server instance. I've added a SQL login user account and SQL authentication is on. I've given this account access to the server and database and granted CONNET, SELECT, UPDATE, etc access rights, I've even made them admin and owner etc and I've opened the default ports in the firewall.

I'm using a connection string in this format

"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=fgdgfdgs;"

Every time I connect the error message says the targed machine actively refused the connection.

Anyone got an idea or a checklist of how to set up remotre connections ?

Thanks

I've got it to work!!

The connection string needed the SQL Instance, doh! Learned a lesson there but that's a result.

However I cannot specify a port and have to use the default. I've tried...

Data Source=190.190.200.100,1433\SQLInstance

Data Source=190.190.200.100\SQLInstance,1433

and neither are working, not a problem but any idea.

Remote Connection with SQL Server 2005 Developer Edition

Can I do a Remote Connection with SQL Sever 2005 Developer Editio?

Yes. You have to enable the remoteconnections first:

http://www.sqlserver2005.de/sharedFiles/remoteconnection.jpg

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Remote connection to SSIS fails with Access is Denied

I'm new to SSIS and I'm having problems getting a remote connection to the SSIS service using Management Studio on my workstation. If I terminal Service onto the Server I have no problems connecting to SSIS, but if I try to connect remotely I get the "Access is denied" error message. I have completed the following steps:

To configure rights for remote users on Windows Server 2003 or Windows XP

1. If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.

2. Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.

3. Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.

4. Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.

5. Right-click on MsDtsServer and select Properties.

6. In the MsDtsServer Properties dialog box, select the Security tab.

7. Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.

8. In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.

9. Click OK to close the dialog box. Close the MMC snap-in.

10. Restart the Integration Services service.

But I still get the Access is denied error from my workstation?

I have Power User rights on the server and I'm a sysadmin in the database instance. The SSIS packages I am trying to access are stored in the database. If I add myself to the local administrators group on the server I CAN get remote access, but this is not an acceptable solution in our production environment.

Thanks for any help

Please search the forums... You'd find your answer there, however:

http://www.ssistalk.com/2007/04/13/ssis-access-is-denied-when-connection-to-remote-ssis-service/|||Thank you

Remote Connection to SSIS

Hello.

I am trying to remotely connect to SSIS from my PC using windows authentication in SQL mgmt studio and I keep getting the following error message:

Cannot connect to <server>

Additional Information:

Failed to retrieve date for this request. (Microsoft.SqlServer.SmoEnum)

Connect to SSIS service on machine "<server>" failed: The RPC server is unavailable.

I do not get the same error when I connect to the database engine, just SSIS. I don't have a firewall inbetween the machines either so it can't be that.

Has anyone else had a similar problem? if so, I would be grateful if you can you tell me how you got around it

Thanks

See this thread where I answered a similar question regarding not being able to connect to SSIS from Management Studio, I'm assuming this may be your problem:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=254204&SiteID=1

HTH

|||

I get the same RPC error, but one of my co-workers doesn't get the error, and he can connect to SSIS from his desktop, but I cannot. I assume I don't need to change the .i' GAD 0l3/2e3/2 006o Rn the server since he has no problem connecting.

Any other ideas ?

Remote Connection to SSIS

Hello.

I am trying to remotely connect to SSIS from my PC using windows authentication in SQL mgmt studio and I keep getting the following error message:

Cannot connect to <server>

Additional Information:

Failed to retrieve date for this request. (Microsoft.SqlServer.SmoEnum)

Connect to SSIS service on machine "<server>" failed: The RPC server is unavailable.

I do not get the same error when I connect to the database engine, just SSIS. I don't have a firewall inbetween the machines either so it can't be that.

Has anyone else had a similar problem? if so, I would be grateful if you can you tell me how you got around it

Thanks

See this thread where I answered a similar question regarding not being able to connect to SSIS from Management Studio, I'm assuming this may be your problem:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=254204&SiteID=1

HTH

|||

I get the same RPC error, but one of my co-workers doesn't get the error, and he can connect to SSIS from his desktop, but I cannot. I assume I don't need to change the .i' GAD 0l3/2e3/2 006o Rn the server since he has no problem connecting.

Any other ideas ?

Remote connection to SqlServer

Hello Guys
We are developing a CRM application using Delphi Front end &
Sqlserver as back end which is
residing on Windows 2000 server. I need a best possible solution,
if this CRM app fornt end is installed in different Location and
making connection to the Sqlserver which is altogether in different site...
Thanks
Chandru
There is usually no problem at all using the OLEDB SQL-Server driver over
the internet if the port 1433 is not blocked by a firewall or if you are
able to setup a proxy server. The transport layer will be totally
transparent to your application.
S. L.
"Chandru" <chandru@.knigthoodcorporate.com> wrote in message
news:%23OnHId0xEHA.1168@.TK2MSFTNGP10.phx.gbl...
> Hello Guys
> We are developing a CRM application using Delphi Front end &
> Sqlserver as back end which is
> residing on Windows 2000 server. I need a best possible solution,
> if this CRM app fornt end is installed in different Location and
> making connection to the Sqlserver which is altogether in different
> site...
>
> Thanks
> Chandru
>
>
>

Remote connection to SqlServer

Hello Guys
We are developing a CRM application using Delphi Front end &
Sqlserver as back end which is
residing on Windows 2000 server. I need a best possible solution,
if this CRM app fornt end is installed in different Location and
making connection to the Sqlserver which is altogether in different site...
Thanks
ChandruThere is usually no problem at all using the OLEDB SQL-Server driver over
the internet if the port 1433 is not blocked by a firewall or if you are
able to setup a proxy server. The transport layer will be totally
transparent to your application.
S. L.
"Chandru" <chandru@.knigthoodcorporate.com> wrote in message
news:%23OnHId0xEHA.1168@.TK2MSFTNGP10.phx.gbl...
> Hello Guys
> We are developing a CRM application using Delphi Front end &
> Sqlserver as back end which is
> residing on Windows 2000 server. I need a best possible solution,
> if this CRM app fornt end is installed in different Location and
> making connection to the Sqlserver which is altogether in different
> site...
>
> Thanks
> Chandru
>
>
>

Remote connection to SQLEXPRESS doesn't work!

Hello,

OK. It is not possible for me to get remote connection to a ssqlexpress database (error 40). I tried everything posted in this forum, MSDN site etc., I used named pipes, TCP/IP ports, disabled Zone Alarm etc. it is just not working. I can coonect the Northwind database using SQL Server Management Studio Express CTP without any problem. And I am trying to learn creating databases, tables, stored procedures etc. by using code samples given on the MSDN site.

In at least two examples given examples somehow try to use remote connections to SQLEXPRESS databases in the local computer in order to study stored procedures. Since remote connection isn't working I have been trying to do same thing by connecting the database directly not over a network. I do not know how to do it. The part of the code that does the connection is below. Please help. I am stuck.

Athena

The following code is from:

http://msdn2.microsoft.com/en-us/library/5181xbwd(VS.80).aspx

the connection string is:

' Initialize constants for connecting to the database

' and displaying a connection error to the user.

Protected Const SqlConnectionString As String = _

"Server=(local);" & _

"DataBase=;" & _

"Integrated Security=SSPI"

the code:

' This routine executes a SQL statement that deletes the database (if it exists)

' and then creates it.

Private Sub CreateDatabase()

Dim sqlStatement As String = _

"IF EXISTS (" & _

"SELECT * " & _

"FROM master..sysdatabases " & _

"WHERE Name = 'HowToDemo')" & vbCrLf & _

"DROP DATABASE HowToDemo" & vbCrLf & _

"CREATE DATABASE HowToDemo"

' Display a status message saying that we're attempting to connect.

' This only needs to be done the very first time a connection is

' attempted. After we've determined that MSDE or SQL Server is

' installed, this message no longer needs to be displayed.

Dim statusForm As New Status()

If Not didPreviouslyConnect Then

statusForm.Show("Connecting to SQL Server")

End If

' Attempt to connect to the SQL server instance.

Try

' The SqlConnection class allows you to communicate with SQL Server.

' The constructor accepts a connection string as an argument. This

' connection string uses Integrated Security, which means that you

' must have a login in SQL Server, or be part of the Administrators

' group for this to work.

Dim connection As New SqlConnection(connectionString)

' A SqlCommand object is used to execute the SQL commands.

Dim cmd As New SqlCommand(sqlStatement, connection)

' Open the connection, execute the command, and close the

' connection. It is more efficient to ExecuteNonQuery when data is

' not being returned.

connection.Open()

cmd.ExecuteNonQuery()

connection.Close()

' Data has been successfully submitted.

didPreviouslyConnect = True

didCreateTable = True

statusForm.Close()

' Show the controls for the next step.

lblArrow1.Visible = True

lblStep2.Enabled = True

btnCreateTable.Enabled = True

MsgBox("Database 'HowToDemo' successfully created.", MsgBoxStyle.OKOnly, "Database Creation Status")

Catch sqlExc As SqlException

MsgBox(sqlExc.Message, MsgBoxStyle.OKOnly, "SQL Exception Error")

Catch exc As Exception

' Unable to connect to SQL Server or MSDE

statusForm.Close()

MsgBox(exc.Message, MsgBoxStyle.OKOnly, "Connection failed.")

End Try

End Sub

It looks as though your connection string is missing the instance name of SQLEXPRESS

Server=(local)\SQLEXPRESS

Check our our blog post on troubleshooting this here.

http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

hope this helps,

Brad Sarsfield

|||

Thank you Brad. \SQLEXPRESS solved the problem!

If I solve the "The schema returned by the new query differs from the base query" error when I try to create search queries, I can continue to learn SQLServer programming!

Thanks a lot.

Cem

Remote Connection to SQl Server Express Via ADO

Dear All,

I have TWO PCs, one of them having SQL Server 2005 Express

i get sure the TCP is enables , Active, Remote conneciton is enabled via TCP only

from VS2005 (C#) i can add new sqlconnection contorl and configure it with any of the installed DBs

but when i got to the Other PC with VS2005 as well, i can't configure it

all i can do is to see the server inthe server list.. but NO databases are retieved at all in the Select database drop down menu

any solution or suggestion

Do you use window authentication? Maybe there is a problem with that. Why don't you try creating a database user and try logging in with it?

Remote Connection to SQL Server Express

Hi ,

After reading the SQLexpress weblog , I tried the solution and its still not working

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

The client computer gives me this:
Run-Time error -2147217843 (80040e4d)

Log-in failed for user "Kit\Guest"

The connection string in the ADO object in my testing VB6 program is:

my_connection.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=kit;Initial Catalog=AdelaideMushroom;Data Source=KIT\SQLEXPRESS"

I am confused with the error massage as my conncection string is set to connect on UserID = Kit

I am not sure what is gone wrong. The same VB6 program runs perfectly on host machine

I am not sure is there anything to do with SQL Config. Manager's SQL server 2005 service's Log-ON setting:

Log On as - Build-in account: Network service

could Anyone who managed to get remote connection work give me some advise? I am desperate. have read that weblog many times and do not know how to get it work


If you are using WIndows Authentication (with specifying the SSPI part in the connection string) UserId will be ignored, because this is the SQL Server Authentication. The problem that you are experiencing with the Guets user is based on the setting that you probably have Windows XP and enabled the "Simply File and Printer Sharing". You can disable that by navigating through Explorer > Tools > Folder Options > View > Scroll down to the end , deactivate "Simple file sharing"

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Thank you for the answer

I will give it a try when I get back to office tomorrow

|||

Sorry.... but I still get the same error message

I have firewall of host computer disabled, it connects ok with this command

C:\Documents and Settings\Kit>sqlcmd -E -S KIT\SQLEXPRESS,2301

I have simple file sharing disabled, I no longer see any folder visible on my computer (execept printer & fax and Scheduled Tasks) in Map Network drive.

Seems that the client computer has found KIT\SQLEXPRESS but somehow the host computer direct the client computer to Kit\Guest

The host computer User Account shows there are 3 accounts: KIT (myself), SQLDEBUGGER and Guest. I cannot get rid of Guest tough

I've tried turn firewall off on both computer. Same result

Any idea ?

remote connection to sql server express

I have installed SQL server 2005 express on my main server.

It has been installed with windows authentication.

I can use sqlcmd -S machinename\instancename to connect to the server, but when I try to connect from another machine on my lan, I get the error about user not part of trusted sql connection msg 18452 level 14 state 1

I have tried using a windows username and password from my server to access it but that fails also.

Any help would be appreciated

Hi,

I don′t know if you specified the Windows username and password , because you can′t specify this within SQLCMD; if you use Username and password with SQLCMD you always switch to SQL Server authentication which can be not allowed on the server. If you want to use Windows authewntication, you have to log on as the appropiate user and then use SQLCMD or otherwise you can use the runas command of WIndows to impersonate yourself as the user while executing SQLCMD. Otherwise you can′t use Windows authentication.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Remote Connection to SQL Server 2005 over Windows Server 2003

Hi Fellows!
I am trying to establish a connection between SQL Server 2005 Server
which is setup on Windows Server 2003 along with Visual Studio 2005.
The problem is that when i try to connect it through management
studio using <servername>/databasename method , i become successfull
and got everything. However , if i use ipaddress scheme like
<ipaddress>/databasename , it failed by saying popular error "Error no
:26 Server is not available , ....." along with description "sqr
server might be need to enable for remote access"
I have checked all the ip configuration and also allowed the port
1433, 1444, 1500 (my own named isntance port) and 1743 (the port i
found from the Log of the server).
now the problem is that i am still unable to connect my client to my
server using ipaddress.
Can somebody sugggest me about this porblem.
i am really greatfull to you for this.
Regards,
UsmanBy <servername>/databasename, I suppose you meant
<servername>\<instancename>. The former should not have worked.
If <servername>\<instancename> works, <ipaddress>\<instancename> should work
too unless (1) <ipaddress> is not really the IP address of the server, (2)
<servername> is resolved by DNS/WINS/Host file to something other than
<ipaddress>, or (3) you have an alias <ipaddress>\<instancename> that points
to a nonexistent/nonactive instance.
BTW, I'm curious as to why you would want to use <ipaddress>\<instancename>?
I don't believe this is a common approach.
Linchi
"gripusa" wrote:

> Hi Fellows!
> I am trying to establish a connection between SQL Server 2005 Server
> which is setup on Windows Server 2003 along with Visual Studio 2005.
> The problem is that when i try to connect it through management
> studio using <servername>/databasename method , i become successfull
> and got everything. However , if i use ipaddress scheme like
> <ipaddress>/databasename , it failed by saying popular error "Error no
> :26 Server is not available , ....." along with description "sqr
> server might be need to enable for remote access"
> I have checked all the ip configuration and also allowed the port
> 1433, 1444, 1500 (my own named isntance port) and 1743 (the port i
> found from the Log of the server).
> now the problem is that i am still unable to connect my client to my
> server using ipaddress.
> Can somebody sugggest me about this porblem.
> i am really greatfull to you for this.
> Regards,
> Usman
>

Remote Connection to SQL Server 2005 over Windows Server 2003

Hi Fellows!
I am trying to establish a connection between SQL Server 2005 Server
which is setup on Windows Server 2003 along with Visual Studio 2005.
The problem is that when i try to connect it through management
studio using <servername>/databasename method , i become successfull
and got everything. However , if i use ipaddress scheme like
<ipaddress>/databasename , it failed by saying popular error "Error no
:26 Server is not available , ....." along with description "sqr
server might be need to enable for remote access"
I have checked all the ip configuration and also allowed the port
1433, 1444, 1500 (my own named isntance port) and 1743 (the port i
found from the Log of the server).
now the problem is that i am still unable to connect my client to my
server using ipaddress.
Can somebody sugggest me about this porblem.
i am really greatfull to you for this.
Regards,
UsmanBy <servername>/databasename, I suppose you meant
<servername>\<instancename>. The former should not have worked.
If <servername>\<instancename> works, <ipaddress>\<instancename> should work
too unless (1) <ipaddress> is not really the IP address of the server, (2)
<servername> is resolved by DNS/WINS/Host file to something other than
<ipaddress>, or (3) you have an alias <ipaddress>\<instancename> that points
to a nonexistent/nonactive instance.
BTW, I'm curious as to why you would want to use <ipaddress>\<instancename>?
I don't believe this is a common approach.
Linchi
"gripusa" wrote:
> Hi Fellows!
> I am trying to establish a connection between SQL Server 2005 Server
> which is setup on Windows Server 2003 along with Visual Studio 2005.
> The problem is that when i try to connect it through management
> studio using <servername>/databasename method , i become successfull
> and got everything. However , if i use ipaddress scheme like
> <ipaddress>/databasename , it failed by saying popular error "Error no
> :26 Server is not available , ....." along with description "sqr
> server might be need to enable for remote access"
> I have checked all the ip configuration and also allowed the port
> 1433, 1444, 1500 (my own named isntance port) and 1743 (the port i
> found from the Log of the server).
> now the problem is that i am still unable to connect my client to my
> server using ipaddress.
> Can somebody sugggest me about this porblem.
> i am really greatfull to you for this.
> Regards,
> Usman
>

Remote Connection to SQL Server 2005 on Windows 2003

Hi Fellows!
I am trying to connect a SQL Server 2005 Server setup on Windows Server 2003 and failing. I have checked few other related posts but i think so i am still unable to find the solution.
My configuration and problem is following;
I want to enable access for my SQL Server to my network as well as through internet using our Fix IP address server.
Configurations:
Server: Win XP Server 2003 along with VS 2005 and SQL Server 2005
Clients : WinXP Laptops (home addition) installed SQL Server Connectivity tools and Express addition and also community edition of Management Studio for Express edition.
I am able to connect the server when i try to use my servername but failed when i try to use ipaddress in the connection.
i tired with telnet, the same problem occurs as i can login by using the network machine name but unable to login by using the local or external ip address.
I have checked with my router configuration and i have allowd both the standard ip and the ip i have given to my named instance and also the port which i have seen in the server log , so i am still unable to find why i am not able to connect it.
Can some one will guide me, i will be really thankfull to him.

start>program>microsoft sqlserver 2005>configuration tools>sql server surface area configuration

>configuration for service and connection>remote connections

>check local and remote

>check both tcp/ip and named pipes

|||Thanks for your reply, i tried to use Surface area tool for my client but it is unable to connect to that client. Especially it also becomes difficult when i am gona operate across the network . (Actually server is in our headquarter and client is in other office).|||

hi,

kindly check if port 1433 is left opened in your firewall

regards,

joey

|||

Try adding sqlservr.exe (present in \MSSQL\Binn directory for the default instance and \MSSQL$instance_name\Binn directory for the named instance) in the list of allowed firewall exceptions on the Windows 2003 box.

Regards,

Rajesh

Remote Connection to SQL Server 2005 on Windows 2003

Hi Fellows!
I am trying to connect a SQL Server 2005 Server setup on Windows Server 2003 and failing. I have checked few other related posts but i think so i am still unable to find the solution.
My configuration and problem is following;
I want to enable access for my SQL Server to my network as well as through internet using our Fix IP address server.
Configurations:
Server: Win XP Server 2003 along with VS 2005 and SQL Server 2005
Clients : WinXP Laptops (home addition) installed SQL Server Connectivity tools and Express addition and also community edition of Management Studio for Express edition.
I am able to connect the server when i try to use my servername but failed when i try to use ipaddress in the connection.
i tired with telnet, the same problem occurs as i can login by using the network machine name but unable to login by using the local or external ip address.
I have checked with my router configuration and i have allowd both the standard ip and the ip i have given to my named instance and also the port which i have seen in the server log , so i am still unable to find why i am not able to connect it.
Can some one will guide me, i will be really thankfull to him.

start>program>microsoft sqlserver 2005>configuration tools>sql server surface area configuration

>configuration for service and connection>remote connections

>check local and remote

>check both tcp/ip and named pipes

|||Thanks for your reply, i tried to use Surface area tool for my client but it is unable to connect to that client. Especially it also becomes difficult when i am gona operate across the network . (Actually server is in our headquarter and client is in other office).

|||

hi,

kindly check if port 1433 is left opened in your firewall

regards,

joey

|||

Try adding sqlservr.exe (present in \MSSQL\Binn directory for the default instance and \MSSQL$instance_name\Binn directory for the named instance) in the list of allowed firewall exceptions on the Windows 2003 box.

Regards,

Rajesh

Remote connection to SQL Server 2005

I am unable to connect to a SQL Server 2005 instance running remotely
on a Windows 2003 Server machine behind a router. I can connect to the
instance via SQL Server Management Studio Express when I place the
server machine in the DMZ, but I cannot connect when I forward traffic
on port 1433. Note that I am able to forward Remote Desktop requests to
my server, so I know that port forwarding works in other cases.
Any suggestions? I'd rather not leave my server machine in the DMZ.
Thanks for the help.Only the default (un-named) instance listens on port 1433. If you have a
named instance or are running SQL Express, which is a named instance named
(SQLEXPRESS) you are listening on dynamic ports.
For more ideas, run through my short connectivity tutorial at
http://msdn2.microsoft.com/en-us/library/ms345318(SQL.90).aspx
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
<bostonguy@.gmail.com> wrote in message
news:1147702273.174968.231850@.i40g2000cwc.googlegroups.com...
>I am unable to connect to a SQL Server 2005 instance running remotely
> on a Windows 2003 Server machine behind a router. I can connect to the
> instance via SQL Server Management Studio Express when I place the
> server machine in the DMZ, but I cannot connect when I forward traffic
> on port 1433. Note that I am able to forward Remote Desktop requests to
> my server, so I know that port forwarding works in other cases.
> Any suggestions? I'd rather not leave my server machine in the DMZ.
> Thanks for the help.
>|||Is it recommended to use something other than port 1433 when
configuring a static port for a named instance like (SQLEXPRESS)?
Thanks,
John
Rick Byham [MS] wrote:[vbcol=seagreen]
> Only the default (un-named) instance listens on port 1433. If you have a
> named instance or are running SQL Express, which is a named instance named
> (SQLEXPRESS) you are listening on dynamic ports.
> For more ideas, run through my short connectivity tutorial at
> http://msdn2.microsoft.com/en-us/library/ms345318(SQL.90).aspx
> --
> Rick Byham
> MCDBA, MCSE, MCSA
> Documentation Manager,
> Microsoft, SQL Server Books Online
> This posting is provided "as is" with
> no warranties, and confers no rights.
> <bostonguy@.gmail.com> wrote in message
> news:1147702273.174968.231850@.i40g2000cwc.googlegroups.com...|||There is no particular reason to use a different port number. Some people
use different numbers in case there is another SQL Server installed on the
system. Some people like to make it harder for viruses or hackers that are
targeting port 1433, but using a different port doesn't confuse them that
much.
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"John" <john@.destinytours.com> wrote in message
news:1147905901.881404.59600@.j55g2000cwa.googlegroups.com...
> Is it recommended to use something other than port 1433 when
> configuring a static port for a named instance like (SQLEXPRESS)?
> Thanks,
> John
> Rick Byham [MS] wrote:
>|||Yes, I experienced this first hand when I discovered someone from
Amsterdam trying to hack into my server with repeated login attemps
(trying every few seconds).
Rick Byham [MS] wrote:[vbcol=seagreen]
> There is no particular reason to use a different port number. Some people
> use different numbers in case there is another SQL Server installed on the
> system. Some people like to make it harder for viruses or hackers that are
> targeting port 1433, but using a different port doesn't confuse them that
> much.
> --
> Rick Byham
> MCDBA, MCSE, MCSA
> Documentation Manager,
> Microsoft, SQL Server Books Online
> This posting is provided "as is" with
> no warranties, and confers no rights.
> "John" <john@.destinytours.com> wrote in message
> news:1147905901.881404.59600@.j55g2000cwa.googlegroups.com...

Remote connection to MSDE

I'm trying to connect to the SharePoint MSDE database from Windows XP
SP2 machine over Ethernet LAN. I was getting server not found or access
denied, until I enabled TCP/IP via svrnetcn.exe. Now, I'm getting
OK
Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
exist or access denied.
Any ideas?
I just did an netstat -an and I don't see the server listening on 1433.
What am I not doing?
|||Ok, here's what i've discovered:
The svrnetcn tool has no effect. I make a change and check the logs,
but nothing happens. What would be causing this?
|||Did you restart after enabling TCP/IP? Is it a default
instance or a named instance? Can you connect locally when
on the machine with MSDE?
-Sue
On 25 Jan 2006 14:21:43 -0800, "laurin1" <laurin1@.osai.com>
wrote:

>I just did an netstat -an and I don't see the server listening on 1433.
>What am I not doing?
|||Yes. It's a named instance (SHAREPOINT). I can connect locally.
|||Make sure you aren't having firewall issues.
Can you ping the MSDE box from the other box?
Check the listening port for the MSDE instance - when you
start up MSDE, it logs what protocols, ports it is listening
on in the error log.
Try to telnet to the port it's listening on.
Verify the login being used.
And if those don't get you anywhere, you can find additional
troubleshooting steps in the following article:
INF: Potential Causes of the "SQL Server Does Not Exist or
Access Denied" Error Message
http://support.microsoft.com/?id=328306
-Sue
On 26 Jan 2006 05:41:03 -0800, "laurin1" <laurin1@.osai.com>
wrote:

>Yes. It's a named instance (SHAREPOINT). I can connect locally.
|||I just found out that this is WMSDE, not MSDE, which can only be
connected to remotely. No matter what changes I make, the server will
not listen on 1433.
|||If it is indeed WMSDE, it only supports local connections so it will never
listen on a port. WMSDE is an OS component that can only be connected to by
selected Microsoft software running locally.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"laurin1" <laurin1@.osai.com> wrote in message
news:1138379664.059706.236830@.g14g2000cwa.googlegr oups.com...
>I just found out that this is WMSDE, not MSDE, which can only be
> connected to remotely. No matter what changes I make, the server will
> not listen on 1433.
>

Remote connection to MSDE

I'm trying to connect to the SharePoint MSDE database from Windows XP
SP2 machine over Ethernet LAN. I was getting server not found or access
denied, until I enabled TCP/IP via svrnetcn.exe. Now, I'm getting
OK
Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpe
n
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server do
es not
exist or access denied.
Any ideas?I just did an netstat -an and I don't see the server listening on 1433.
What am I not doing?|||Ok, here's what i've discovered:
The svrnetcn tool has no effect. I make a change and check the logs,
but nothing happens. What would be causing this?|||Did you restart after enabling TCP/IP? Is it a default
instance or a named instance? Can you connect locally when
on the machine with MSDE?
-Sue
On 25 Jan 2006 14:21:43 -0800, "laurin1" <laurin1@.osai.com>
wrote:

>I just did an netstat -an and I don't see the server listening on 1433.
>What am I not doing?|||Yes. It's a named instance (SHAREPOINT). I can connect locally.|||Make sure you aren't having firewall issues.
Can you ping the MSDE box from the other box?
Check the listening port for the MSDE instance - when you
start up MSDE, it logs what protocols, ports it is listening
on in the error log.
Try to telnet to the port it's listening on.
Verify the login being used.
And if those don't get you anywhere, you can find additional
troubleshooting steps in the following article:
INF: Potential Causes of the "SQL Server Does Not Exist or
Access Denied" Error Message
http://support.microsoft.com/?id=328306
-Sue
On 26 Jan 2006 05:41:03 -0800, "laurin1" <laurin1@.osai.com>
wrote:

>Yes. It's a named instance (SHAREPOINT). I can connect locally.|||I just found out that this is WMSDE, not MSDE, which can only be
connected to remotely. No matter what changes I make, the server will
not listen on 1433.|||If it is indeed WMSDE, it only supports local connections so it will never
listen on a port. WMSDE is an OS component that can only be connected to by
selected Microsoft software running locally.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"laurin1" <laurin1@.osai.com> wrote in message
news:1138379664.059706.236830@.g14g2000cwa.googlegroups.com...
>I just found out that this is WMSDE, not MSDE, which can only be
> connected to remotely. No matter what changes I make, the server will
> not listen on 1433.
>|||somebody on this forum "sameer" gave me a clue that
1433 is not listened on due to the "slammer" virus requiring SP1 for SQL
Server 2005...I am just begiing on this so what do I know
"Roger Wolter[MSFT]" wrote:

> If it is indeed WMSDE, it only supports local connections so it will never
> listen on a port. WMSDE is an OS component that can only be connected to
by
> selected Microsoft software running locally.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "laurin1" <laurin1@.osai.com> wrote in message
> news:1138379664.059706.236830@.g14g2000cwa.googlegroups.com...
>
>

Remote Connection to file-based Express DB

Hi,

This might be obvious - but if so I've missed it so far.

Is it possible to connect remotely to an Express database used in a website which is created in the App_Data folder by VWDEE 2005 and attached by filename?

As far as I can tell these databases don't have a name and aren't mounted until an attempt is made to access them. I certainly can't see any sign of it in SQL Management Studio.

Thanks,

Dale

Hi Dale,

SQL Express only connects to local files; you can't connect as you suggest.

Regards,

Mike Wachal
SQL Express team

|||

Hi Mike - thanks for your reply.

Just to clarify further if I use an SQL Server 2005 Express database as part of my web app hosted with my web site; the only way I can remotely access the data in that database is by physically copying the database locally - essentually the same as if I used an MS Access database?

Cheers,

Mike Wachal - MSFT wrote:

Hi Dale,

SQL Express only connects to local files; you can't connect as you suggest.

Regards,

Mike Wachal
SQL Express team

Remote Connection to file-based Express DB

Hi,

This might be obvious - but if so I've missed it so far.

Is it possible to connect remotely to an Express database used in a website which is created in the App_Data folder by VWDEE 2005 and attached by filename?

As far as I can tell these databases don't have a name and aren't mounted until an attempt is made to access them. I certainly can't see any sign of it in SQL Management Studio.

Thanks,

Dale

Hi Dale,

SQL Express only connects to local files; you can't connect as you suggest.

Regards,

Mike Wachal
SQL Express team

|||

Hi Mike - thanks for your reply.

Just to clarify further if I use an SQL Server 2005 Express database as part of my web app hosted with my web site; the only way I can remotely access the data in that database is by physically copying the database locally - essentually the same as if I used an MS Access database?

Cheers,

Mike Wachal - MSFT wrote:

Hi Dale,

SQL Express only connects to local files; you can't connect as you suggest.

Regards,

Mike Wachal
SQL Express team

Remote Connection to file-based Express DB

Hi,

This might be obvious - but if so I've missed it so far.

Is it possible to connect remotely to an Express database used in a website which is created in the App_Data folder by VWDEE 2005 and attached by filename?

As far as I can tell these databases don't have a name and aren't mounted until an attempt is made to access them. I certainly can't see any sign of it in SQL Management Studio.

Thanks,

Dale

Hi Dale,

SQL Express only connects to local files; you can't connect as you suggest.

Regards,

Mike Wachal
SQL Express team

|||

Hi Mike - thanks for your reply.

Just to clarify further if I use an SQL Server 2005 Express database as part of my web app hosted with my web site; the only way I can remotely access the data in that database is by physically copying the database locally - essentually the same as if I used an MS Access database?

Cheers,

Mike Wachal - MSFT wrote:

Hi Dale,

SQL Express only connects to local files; you can't connect as you suggest.

Regards,

Mike Wachal
SQL Express team

Remote connection to database on same PC?

I am running the SQL express advanced on the same PC that also hosts my Visual Basic Express application. I have user instances turned off and full-text enabled and working from within a SQL query window. However, I can not create a working connection to the database using the data source wizard from the server explorer within VB.NET. Since this is on the same PC, do I need to enable anything other than shared memory? I get the error: Provider SQL Network Interfaces, error 26 Error locating server/instance.

DeBug

hi DeBug,

if you are using VBExpress, I've heard the data source wizard should not be able to use connections other than user instances.. you should modify the connection context in your code, pointing to the "shared" SQLExpress data source..

personally I'm not able to help more, as I only own the full VS edition..

regards

|||

Hi,

when you say you turned user instances off, do you mean that you installed the default instance and not a named instance?

What Data source, Servername and log on type did you choose when you tried to add the SQL server to the server explorer?

Are you running VB.Net in the same windows account you installed SQL Express with?

--
SvenC

|||

I believe that user instances are disabled for use with full-text search so I set the User Instance to False. The following connection string both fail:

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SGExpress.mdf";Integrated Security=True;Connect Timeout=30;User Instance=False

Data Source=.\MSSQLSERVER;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SGExpress.mdf";Integrated Security=True;Connect Timeout=30;User Instance=False

I installed VB and SQL Express using my administrative user account logged on locally to the PC. I'm am not being logged into any domain server but only to the local PC.

DeBug

|||

Update: I can connect to the database using the following code in a VB.NET application but not with the Data Source Configuration Wizard. The wizard will not allow me to enter ".\" where it assigns the ".\SQLExpress" and other instances.

Dim conn As New SqlConnection()

conn.ConnectionString = "Data Source=.\;Initial Catalog=SGExpress;Integrated Security=True;Integrated Security=True"

conn.Open()

Me.Text = conn.ServerVersion

conn.Close()

The Microsoft SQL Server Reporting Service wizard allows me to produce and connect the following xml DataSource.rds file:

<?xml version="1.0" encoding="utf-8"?>
<RptDataSource xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Name>DataSourcePlats</Name>
<DataSourceID>cf8ea427-70f2-4a10-8989-61ed5f9bd74c</DataSourceID>
<ConnectionProperties>
<Extension>SQL</Extension>
<ConnectString>Data Source=.\;Initial Catalog=SGExpress</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</RptDataSource>

However, even though I saved the above data source as Shared, it does not show up in VB.NET.

How can I manually create a DataSource for my Visual Basic Express applications? I believe this problem is related to the fact that my SQL Express was installed to allow for full-text indexing and does not have indivual instances.

DeBug

|||

Did you try to use only . instead of .\

You might also try your real computer name.

How did you install SQL Server? Did you specify an instance name? Did you install more than one instance? What services do you see in service manager (Start->Run : services.msc) which start with "SQL Server". Please post the exact names.

--
SvenC

|||

The wizard will only let me select an instance from the drop down box. I can not edit that property to key in: ".", " .\", or my computer name.

I installed SQL Server Express Advanced using the EXE downloaded from the MSDN web site. It created a directory like "C:\d3705b3534f0937859a" and installs from there. I do not recall specifing a instance name on the last intall as per the full-text requirements. I have however used the SQL Express (not advanced) with an instance name on this PC. That version of SQL Express was removed using the ADD/Remove and the newer SQL Express with full-text search was then installed.

Service Manager:

SQL Server FullText Search (SQLEXPRESS) - stopped, auto

SQL Server (MSSQLSERVER) - started, auto

SQL Server Active Directory Helper - stopped, disabled

SQL Server Browser - started, auto

SQL Server FullText Search (MSSQLSERVER) -started, auto

SQL Server VSS Writer - stopped, manual

I can connect using ADO.NET in Visual Basic Express using just code but fail at creating a data connection via the wizard.

DeBug

|||

Hmm, the way you use the server explorer in VB.Net Express seems to be different to the full version of Visual Studio 2005 I use. In the server explorer I see a top node "Data Connections" where I can right click to add a connection. In the following dialog I can enter the computer name where SQL server is running. The data source field is a bit misleading as I can only choose which provider to use. I have a few screenshots how it looks on my machine:

http://i112.photobucket.com/albums/n193/Sven_Carstensen/vs_Server1.gif

http://i112.photobucket.com/albums/n193/Sven_Carstensen/vs_Server2.gif

http://i112.photobucket.com/albums/n193/Sven_Carstensen/vs_Server3.gif

Is that comparable to your server explorer UI? Otherwise I hope someone with VB.Net Express can help.

--
SvenC

|||

Visual Basic Express has a "Database Explorer" which contains a collection of Data Connections. I can right click the Database Explorer pane and choose Add Connection. This launches the Add Connection wizard which allows me to select the data provider, the database file, and other connection parameters. When I click the Test Connection button, it always fails. However, if I code a connection with ADO.NET as noted in my previous posts, I can in fact connect and display a query in a DataGridView control. I've used the wizard in the past to create connections and I'm puzzled why it is failing now. The only thing I changed when removing and then reinstalling SQL Express with Advanced Services, is installing using the default instance and disabling user instances as per the full-text documentation. If I right click in the code window of Visual Basic Express and insert the snippet "create a local SQL connection to SQL Express" it also fails with what I think should be a correct connection string. If I insert the snippet "create a server based connection SQL Server" then it works correctly. I think the wizard is trying to create the same type as the local SQL connection to SQL Express and fails for the same reason. I'm struggling to discover that reason.

DeBug

|||

That sounds like a limitiation in the Express edition of VB. The database explorer seems to always expect the named instance SQLExpress which you now no longer have.

You might need to use SQL Server Management Studio Express instead of the build in Database Explorer to manage your SQL server. That should give you full control to what server and instance you want to connect.

Maybe some Microsoft person has better details about using the Database Explorer with other instance names as SQLExpress?

--
SvenC

|||

Hi Doug,

Andrea is right, VB Express uses User Instances by default, although I would have expected a different error that your getting.

If you're trying to create a database directly in your project (i.e. Right-click in the project folder and use the Add New Item | Database functionality) you will have to use User Instance, that's the only way it works.

If you've already created a database on the parent instance of your server, then you can make a connection to it in the Database Manager, but you'll need to go into the Advanced properties and change User Instance to false. You will have to point at the actual database file, even though it is already attached, because that's just the way VB Express is designed to work. (In short, both VB Express and C# Express were designed with the assumption that they would be used only to create single user applications with data stored locally by using User Instances. Because of this, the connections work in a specific way.)

Another possibility given the error, is that you've installed your Instance of SQL Express to a non-default Instance Name. (something other than SQLEXPRESS) If that is the case, you'll need to modify the properties of your VB Express installation to use your Instance Name. By default, VB Express is configured to use SQLEXPRESS as the Name Instance. You can do this on the Tools | Properties menus. Select the check box to show all properties in the dialog and then look in the Data properties.

Mike