Remove Backup Files ?

We are using SQL Server 2000 with SP3 installed.
In the database maintenance plan, we have chosen
to "Remove files older than 1 week" for the production
database.
We would like to know does the setting mean that "SQL
Server will remove backup files generated by the DB
Maitenance Plan will be removed if it is older than 1
week ?". This is because we find that there are some DB
backups (Done in July last year) exist in that directory.
ThanksBelow KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Roger Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:045801c39db5$02521100$a601280a@.phx.gbl...
> We are using SQL Server 2000 with SP3 installed.
> In the database maintenance plan, we have chosen
> to "Remove files older than 1 week" for the production
> database.
> We would like to know does the setting mean that "SQL
> Server will remove backup files generated by the DB
> Maitenance Plan will be removed if it is older than 1
> week ?". This is because we find that there are some DB
> backups (Done in July last year) exist in that directory.
> Thanks|||Dear Tibor,
Thank you for your advice.
However, what I missed in last message is that -
Is it possible to make ad hoc backup of the production
database in the same directory where "Daily backup is
generated by the database maintenance plan that 'Remove
files older than 1 week' option is chosen for the
production database".
This is because if the "Database Maintenance Plan" only
remove backups generated by itself, it is perfect for us
to put ad hoc backup there as we don't want to create
another directory for holding ad hoc backup.
Thanks

Remove anoter SQL 2005 instance

I have installed SQL 2005 Instance on the existing default instance and
noww I will need to remove the second Instance . how can I do that The add
and remove do not have the second instance name.You must have seen the choice for your all SQL Server instances to remove or
change in Add\Remove Programs window.
I guess, you might have taken it wrong. There will be only one item which is
Microsoft SQL Server 2005. Click on it and click Uninstall button. When you
do that you'll see a list of your installed instances of SQL Server and
choose an instance according to your needs and proceed.
--
Ekrem Önsoy
<msnews.microsoft.com> wrote in message
news:%23Ym%23ZGPFIHA.5328@.TK2MSFTNGP05.phx.gbl...
>I have installed SQL 2005 Instance on the existing default instance and
>noww I will need to remove the second Instance . how can I do that The add
>and remove do not have the second instance name.
>

Remotely Register SQL Server 2005 Express in Enterprise Manager

I have SQL Server 2005 Express installed on a windows server 2003 standard edition. I have configured the windows firewall to allow exceptions for sqlservr.exe and sqlbrowser.exe. I have enabled remote and local connections using tcp/ip & Named Pipes through the Sql Server Surface Area Connection utility. I am able to telnet into the server with: telnet <server ip> <1433>.

Here is the strange part: I am able to connect to the Sql Server remotely from a development machine via Sql Server Management Studio Express, but I am unable to connect to the Sql Server from the same development machine by registering it in Sql Server Enterprise Manager...Is it possible to register SQL Express through Enterprise Manager?

When registering the SQL Express DB in Enterprise Manager I tried both just the (IP Address) of the server and the (IP Address\SQLExpress) as the server/host but I am getting the following errors from enterprise manager:

Just the (IP Address) as the server/host:

Invalid Connection.

ConnectionOpen( Invalid Instance())

(IP Address\SQLEXPRESS) as the server/host:

Sql Server does not exist or access denied. ConnectOpen(Connect()).

Any idea why I can’t register/mount the instance remotely in Enterprise Manager

but I can in Sql Server Management Studio Express?

Thanks,

Greg

You can not use the Enterprise management tools with sql 2005, it is only for sql 2000 and below. The connection layer and management objects are different, but the SQL 2005 Management tools can read the lower version systems. Doing this you will notice that some functions can not be completed on the lower versions due to the differences in connections. For example you can not create a new maintenance plan in the sql 2005 tools to work on sql 2000, but you can edit it after it has been created using the old sql enterprise manager.

Remotely Register SQL Server 2005 Express in Enterprise Manager

I have SQL Server 2005 Express installed on a windows server 2003 standard edition. I have configured the windows firewall to allow exceptions for sqlservr.exe and sqlbrowser.exe. I have enabled remote and local connections using tcp/ip & Named Pipes through the Sql Server Surface Area Connection utility. I am able to telnet into the server with: telnet <server ip> <1433>.

Here is the strange part: I am able to connect to the Sql Server remotely from a development machine via Sql Server Management Studio Express, but I am unable to connect to the Sql Server from the same development machine by registering it in Sql Server Enterprise Manager...Is it possible to register SQL Express through Enterprise Manager?

When registering the SQL Express DB in Enterprise Manager I tried both just the (IP Address) of the server and the (IP Address\SQLExpress) as the server/host but I am getting the following errors from enterprise manager:

Just the (IP Address) as the server/host:

Invalid Connection.

ConnectionOpen( Invalid Instance())

(IP Address\SQLEXPRESS) as the server/host:

Sql Server does not exist or access denied. ConnectOpen(Connect()).

Any idea why I can’t register/mount the instance remotely in Enterprise Manager

but I can in Sql Server Management Studio Express?

Thanks,

Greg

You will not be able to register a sql2k5 on EM. EM uses sql2k dmo which can't be used to manage sql2k5.

You will have to use sql2k5 mgmt tool to manage sqlexpress/sql2k5 instances.|||

Hi OJ,

Thanks for the reply, I was afraid this was going to be the case. Well so here is the follow up to my question.

I am using Sql Server Management Studio Express (SSMSE)...so will I be able to connect/register a sql2k server to SSMSE?

Basically I want to export the data from my sql2K DB into my sql2k5 express DB so will I be able to do that with SSMSE or will I need the full version of Sql Server Management Studio?

Thanks,

Greg

|||I haven't explicitly looked at ssmse. However, I believe it uses smo. Thus, it's capable to connecting to sql2k and manage it.sql

Remotely connecting

I am kinda new to this.

Question:
I am trying to set up server.
I have installed SQL 2005 standard.

And now I am trying to log in through TCP/IP..
I went to SQL Server Config Mgmt and Enabled TCP/IP
Double checked IP address and made it Active and enabled it.

Now I tried to connect Management Studio using that IP and it doesnt log in.

Does anyone knows good links to guidelines on how to set up server, login etc.

Thank you very muchDid you restart the service ? See my screencast on www.sqlserver2005.de . You have to restart to make changes effective.

HTH, Jens Suessmeyer.

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

check your surface area configuration for connection. check the ooption that says you want to accept connection from a remote connection. The only connection allowed by default is local

start>programs>micrsoft sql server 2005>configuration tools>surface area configuration>configuration for services and connection>remote connection>local and remote connection

from there you'll know what to do

Remotely Connect to MSDE

Hi
I just installed MSDE over my Windows 2003 box and facing an small problem.
I installed it with SQL security mode and also set a password for user "sa".
Everything looks just fine using "osql" as it uses local address.
But I can't connect to my server from another computer using VS.NET Server Explorer.
It seems as MSDE is set to reject connections from outside by default.
My question is how to enable it so I can connect using my "sa" account.
Appreciate your help in advance.OK. are you trying to connect by IP address or server name? do you have a firewall installed?|||Since I am connecting from outside of the server, I am using IP address.
And also I don't have any firewall installed.|||at a guess I'd say you have only windows authentication enabled. not 100% sure how to change that in MSDE, but I'll scout around and see.

Remote SSIS connection problem

we are trying to remotely adminiter SQL 2005 installed on Server B (actual server names are diferent) from SQL Server Manamagement Studio installed on Server A.

We can connect to everything else except SSIS. When we try to connect to SSIS, it says:

Cannot Connecto to B
Additional Information:
Failed to retrieve data fro this request. (Microsoft.SqlServer.SmoEnum)
Conecct to SSIS Server on machine "B" failed:
The RPC server is unavailable
.
Conecct to SSIS Server on machine "B" failed:
The RPC server is unavailable

from connection dialog on A, we have tried putting FDQN of B.
we are using windows authentication with domain account.

per one of the suggestions, we modified MsDtsSrvr.ini.xml to change the ServerName for MSDB to be one fo the following:
B, .\MSSQLSERVER, B\MSSQLSERVER, .

we also went to DCOM settings of MsDtsServer, under security, Customize for selected for all three Permissions, and under customize settings, B\Administrators was given full access for everything.

also the domain user was added to both machines in the administrators group.

i'll appreciate any ideas.Might there be a firewall enabled on machine B?|||Both are windows 2003 boxes with no firewalls. there is a firewall in between box b and a, and we requested hosting services to open ports 135, and 3882 which they did. we have emailed them back today to make sure that the ports were indeed opened.

btw, i am with mcs. i posted it here since i wasnt sure who to direct this question to, since i did not have any internal BI contacts. i am working on this issue with a client.

Thanks
Ali|||

We are using DCOM, not RPC and port 135 may not be enough.

This port serves as RPC endpoint for initial service discovery, but then the client should be able to communicate via (randomly assigned) port used by MsDtsSrvr.exe

|||

If it always picks a random port, do we have to resort to what is specified in this article (static end points for DCOM ) http://weblogs.asp.net/rhurlbut/archive/2004/03/07/85542.aspx so we can open only 2 ports (or a range of ports in the firewall) ?

|||Or, can we configure a static endpoint for the MsDtsServer.exe from the application properties under component services? is that same as setting the registry to configure port settings?

Thanks
Ali|||

Yes, it creates the same registry key.

|||I have the same problem. Did you find a resolution ? I can connect from my desktop to Analysis Services and Database Engine, just not to SSIS. I haven't seen a useful answer to this anywhere !!!|||

Hi,

I dont know exactly why this error comes but i solved it by removing the server name from the named pipes.

\\.\pipe\sql\query and entered this value in the named pipes field.

and i started working.

from

sufian

Remote SSIS connection problem

we are trying to remotely adminiter SQL 2005 installed on Server B (actual server names are diferent) from SQL Server Manamagement Studio installed on Server A.

We can connect to everything else except SSIS. When we try to connect to SSIS, it says:

Cannot Connecto to B
Additional Information:
Failed to retrieve data fro this request. (Microsoft.SqlServer.SmoEnum)
Conecct to SSIS Server on machine "B" failed:
The RPC server is unavailable
.
Conecct to SSIS Server on machine "B" failed:
The RPC server is unavailable

from connection dialog on A, we have tried putting FDQN of B.
we are using windows authentication with domain account.

per one of the suggestions, we modified MsDtsSrvr.ini.xml to change the ServerName for MSDB to be one fo the following:
B, .\MSSQLSERVER, B\MSSQLSERVER, .

we also went to DCOM settings of MsDtsServer, under security, Customize for selected for all three Permissions, and under customize settings, B\Administrators was given full access for everything.

also the domain user was added to both machines in the administrators group.

i'll appreciate any ideas.Might there be a firewall enabled on machine B?|||Both are windows 2003 boxes with no firewalls. there is a firewall in between box b and a, and we requested hosting services to open ports 135, and 3882 which they did. we have emailed them back today to make sure that the ports were indeed opened.

btw, i am with mcs. i posted it here since i wasnt sure who to direct this question to, since i did not have any internal BI contacts. i am working on this issue with a client.

Thanks
Ali|||

We are using DCOM, not RPC and port 135 may not be enough.

This port serves as RPC endpoint for initial service discovery, but then the client should be able to communicate via (randomly assigned) port used by MsDtsSrvr.exe

|||

If it always picks a random port, do we have to resort to what is specified in this article (static end points for DCOM ) http://weblogs.asp.net/rhurlbut/archive/2004/03/07/85542.aspx so we can open only 2 ports (or a range of ports in the firewall) ?

|||Or, can we configure a static endpoint for the MsDtsServer.exe from the application properties under component services? is that same as setting the registry to configure port settings?

Thanks
Ali|||

Yes, it creates the same registry key.

|||I have the same problem. Did you find a resolution ? I can connect from my desktop to Analysis Services and Database Engine, just not to SSIS. I haven't seen a useful answer to this anywhere !!!|||

Hi,

I dont know exactly why this error comes but i solved it by removing the server name from the named pipes.

\\.\pipe\sql\query and entered this value in the named pipes field.

and i started working.

from

sufian

Remote Sql2000 trough Local Enterpris 7.0

Hi,
is it possible to manage a Remote Sql2000 trough a local installed Entrepise
manager 7.0 ?
GL.No, only the ther way.
HTH, Jens Suessmeyer.|||No, EM 7 cannot connect to SQL Server 2000.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Grard Leclercq" <gl@.pasdemail.fr> wrote in message
news:DiEIf.258469$3P4.7890037@.phobos.telenet-ops.be...
> Hi,
> is it possible to manage a Remote Sql2000 trough a local installed Entrepi
se manager 7.0 ?
> GL.
>

Remote SQL server access

I'm writing a piece of software that is designed to access data from SQL server. The office's server has SQL server version 7 installed. The software works perfectly when running on a computer on the network in the office. I however am trying to work out how to use use the software from my home computer and connect to the SQL server database remotely. I've never done this before and I'm finding it hard to find the right information.

Firstly, is it easy to do this? What do I need to do with the server in the office to allow this to work? Do I need any extra hardware/software? How will my software connect to SQL server? Is it as simple as changing the connection string to something like the server's ip address and sql server name?

I'm writing the software in Visual Studio 2005 professional and programming in VB.

Many thanks.

Well, that depends.

"Firstly, is it easy to do this? "

Yes, this is not that hard. If you going through VPN or direct call connections using a dialin you don′t have to worry. Using the internet as the transport layer, you will have to consider your security archtitecture of your office as if you need to have access to your office you probably will need to open certain ports on the firewall which might protect your company from intruders.

"What do I need to do with the server in the office to allow this to work?"

Well assuming for the next questions that you will use a internet connection to connect to your office, you will need to have a (stateful) firewall configured in your office, which exposes the SQL Server Service to the outsides in some way. For security reasons you should consider locking you access to the office using certain security rules like IPSEC / Certificate bases transports etc.

"Do I need any extra hardware/software? "

That depends. using a firewall and the appropiate security mechanims, you won′t need additional stuff. Having a firewall which is able to do VPN, you will need to have a software which is able to establish a VPN tunnel. For the pure access to SQL Server you won′t need any additional software.

"How will my software connect to SQL server? Is it as simple as changing the connection string to something like the server's ip address and sql server name?"

Yes, thats right, after having a connection to your company network (either through the internet as described below or dial-ins) you can connect to your server easily by changing the servername within the connectionstring to the public name or the IPAdress (and eventually in addition the configured port)

Jens K. Suessmeyer

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

Thank you very much for your assistance. I will try this out in the office in the next couple of days. I'll post another message if I still have questions but hopefully it'll be fine.

Many thanks again,

Sam

Remote SQL server access

I'm writing a piece of software that is designed to access data from SQL server. The office's server has SQL server version 7 installed. The software works perfectly when running on a computer on the network in the office. I however am trying to work out how to use use the software from my home computer and connect to the SQL server database remotely. I've never done this before and I'm finding it hard to find the right information.

Firstly, is it easy to do this? What do I need to do with the server in the office to allow this to work? Do I need any extra hardware/software? How will my software connect to SQL server? Is it as simple as changing the connection string to something like the server's ip address and sql server name?

I'm writing the software in Visual Studio 2005 professional and programming in VB.

Many thanks.

Well, that depends.

"Firstly, is it easy to do this? "

Yes, this is not that hard. If you going through VPN or direct call connections using a dialin you don′t have to worry. Using the internet as the transport layer, you will have to consider your security archtitecture of your office as if you need to have access to your office you probably will need to open certain ports on the firewall which might protect your company from intruders.

"What do I need to do with the server in the office to allow this to work?"

Well assuming for the next questions that you will use a internet connection to connect to your office, you will need to have a (stateful) firewall configured in your office, which exposes the SQL Server Service to the outsides in some way. For security reasons you should consider locking you access to the office using certain security rules like IPSEC / Certificate bases transports etc.

"Do I need any extra hardware/software? "

That depends. using a firewall and the appropiate security mechanims, you won′t need additional stuff. Having a firewall which is able to do VPN, you will need to have a software which is able to establish a VPN tunnel. For the pure access to SQL Server you won′t need any additional software.

"How will my software connect to SQL server? Is it as simple as changing the connection string to something like the server's ip address and sql server name?"

Yes, thats right, after having a connection to your company network (either through the internet as described below or dial-ins) you can connect to your server easily by changing the servername within the connectionstring to the public name or the IPAdress (and eventually in addition the configured port)

Jens K. Suessmeyer

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

Thank you very much for your assistance. I will try this out in the office in the next couple of days. I'll post another message if I still have questions but hopefully it'll be fine.

Many thanks again,

Sam

Remote Server into a named Instance of SQL Server 2005

We are setting up a database server with 2 instances of SQL Server 2005. One for the default and another for Reporting Service. I have them installed but when I try to a registration from another Server for the Reporting Service Instance I can not get in. I am using the sa userid and password, the same one that works when I am on the server but I get an error that says Remote Server connections may not be allowed. When I go to properties the Allow remote connections box is checked. Anyone know what else I need to do to get to the named instance?If you have another instance on the server it probably uses not the default port. If you have chosen another port than the default 1433 for the named instance, you should either start SQL Browser (which will handle request and will forward to the appropiate port on the server) or you directly name it within your connecting application by using the following syntax:

Servername\InstanceName,portnumber.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Remote Reporting Server

Can you have SQL 2005 reporting services installed on a front end web server
without a local SQL engine (i.e. the reports DB is on a remote engine).
Reporting is running and working on the remote back end server which has the
engine as well.
If this is so, can the back end server be SQL 2005 Enterprise SQL 64 bit,
and the front end web server be SQL 2005 Enterprise SQL 32 bitOn Feb 11, 2:06 pm, "John Hiebert" <johndhieb...@.hotmail.com> wrote:
> Can you have SQL 2005 reporting services installed on a front end web server
> without a local SQL engine (i.e. the reports DB is on a remote engine).
> Reporting is running and working on the remote back end server which has the
> engine as well.
> If this is so, can the back end server be SQL 2005 Enterprise SQL 64 bit,
> and the front end web server be SQL 2005 Enterprise SQL 32 bit
As long as your Report Manager and virtual directories for SSRS are on
the front-end server and the back-end server includes the ReportServer
and ReportServerTempDB databases, you should be fine with varying
editions of SQL Server. Of course, your SQL Server license for SSRS is
required in the location of your ReportServer and ReportServerTempDB
databases. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||We have a similar situation but we want SSRS to run on an internal web
server. So teh user has access only to the fron-end web server, and upon a
report request, he'll be forwarded to the internal server for report
processing. The question is how to configure the front-end IIS to run the
reports on the back end IIS.
Thanks,
Gilgamesh
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:0946c772-2790-4993-ba61-b6cad04b4a79@.y5g2000hsf.googlegroups.com...
> On Feb 11, 2:06 pm, "John Hiebert" <johndhieb...@.hotmail.com> wrote:
>> Can you have SQL 2005 reporting services installed on a front end web
>> server
>> without a local SQL engine (i.e. the reports DB is on a remote engine).
>> Reporting is running and working on the remote back end server which has
>> the
>> engine as well.
>> If this is so, can the back end server be SQL 2005 Enterprise SQL 64 bit,
>> and the front end web server be SQL 2005 Enterprise SQL 32 bit
>
> As long as your Report Manager and virtual directories for SSRS are on
> the front-end server and the back-end server includes the ReportServer
> and ReportServerTempDB databases, you should be fine with varying
> editions of SQL Server. Of course, your SQL Server license for SSRS is
> required in the location of your ReportServer and ReportServerTempDB
> databases. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||On Feb 13, 6:56 pm, "Gilgamesh" <Gilgamesh4E...@.aol.com> wrote:
> We have a similar situation but we want SSRS to run on an internal web
> server. So teh user has access only to the fron-end web server, and upon a
> report request, he'll be forwarded to the internal server for report
> processing. The question is how to configure the front-end IIS to run the
> reports on the back end IIS.
> Thanks,
> Gilgamesh
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:0946c772-2790-4993-ba61-b6cad04b4a79@.y5g2000hsf.googlegroups.com...
> > On Feb 11, 2:06 pm, "John Hiebert" <johndhieb...@.hotmail.com> wrote:
> >> Can you have SQL 2005 reporting services installed on a front end web
> >> server
> >> without a local SQL engine (i.e. the reports DB is on a remote engine).
> >> Reporting is running and working on the remote back end server which has
> >> the
> >> engine as well.
> >> If this is so, can the back end server be SQL 2005 Enterprise SQL 64 bit,
> >> and the front end web server be SQL 2005 Enterprise SQL 32 bit
> > As long as your Report Manager and virtual directories for SSRS are on
> > the front-end server and the back-end server includes the ReportServer
> > and ReportServerTempDB databases, you should be fine with varying
> > editions of SQL Server. Of course, your SQL Server license for SSRS is
> > required in the location of your ReportServer and ReportServerTempDB
> > databases. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
The best way to accomplish this architecture is to utilize Render() as
part of the SSRS Web Service. So, have the front-end application be an
ASP.NET application that references the SSRS web service and the
reports from the back-end SSRS installation.
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Remote management of database

trying to connect to remote MSDE database to manage it
--msde instance with DISABLENETWORKPROTOCOLS=0 is installed and working.
--local connection works to confirm username and password
--firewall ports are configured correctly and listening
--remote telnet connection works to confirm ip and port #
--ASP page can connect and return results from query
but tcp/ip does not connect
any suggestions on how to fix/make a tcp/ip connection work ?TJS wrote:
> trying to connect to remote MSDE database to manage it
> --msde instance with DISABLENETWORKPROTOCOLS=0 is installed and working.
> --local connection works to confirm username and password
> --firewall ports are configured correctly and listening
> --remote telnet connection works to confirm ip and port #
> --ASP page can connect and return results from query
> but tcp/ip does not connect
> any suggestions on how to fix/make a tcp/ip connection work ?
>
Well, MSDE (which is a free desktop editiion) normally doesn't work for outs
ide connections(they
don't want you to use the free version as a real server :)),
but i remember that some time ago on a web site i've seen a trick to make it
work. Try googling.|||I searched for a solution before coming here.
"MuZZy" <leyandrew@.yahoo.com> wrote in message
news:A7adnSWJuc_pCKbfRVn-vg@.comcast.com...
> TJS wrote:
> Well, MSDE (which is a free desktop editiion) normally doesn't work for
> outside connections(they don't want you to use the free version as a real
> server :)),
> but i remember that some time ago on a web site i've seen a trick to make
> it work. Try googling.

Remote hosted SQL server - approach?

I have been happily using RS (SQL200) on one of our local servers for some time. However, we have SQL2000 installed on a hosted, managed remote server for some applications we provide.

We need to report on the data so the obvious decision was to get RS installed on the remote machine so I could design and deploy the necessary reports from here to RS on the hosted server.

However, for various reasons (that I do not fully understand) the default install of RS does not work on the remote server and the hsoiting company won't install it in anything other than it's default dormat. So our original plan seems to be stuffed.

What are options here? It was suggested that we could 'replicate' the hosed server data to my 'local' SQL server but that seems to have hit a brick wall too.

What would I need to do to report using the 'local' RS against the 'remote' data - is this possible?

bump?

Remote Desktop

I have msde 2000 installed by one of my applications and
every time i access my pc via Remote Desktop, the msde
initializes? When I'm at my pc locally, it is not an
issue.
What is Remote Desktop doing that is causing this
behaviour and how can i get it to stop?
thanks in advance
jo
Hi Joann,
If you have installed SP3 on MSDE, the network access is
disabled by default (libraries are disabled by default) Windows XP SP2
firewall also blocks any MSSQL services from listening on the network.
It is mentioned as DISABLENETWORKPROTOCOLS switch in the readme
file. You have to enable net libraries.
See these articles:
http://support.microsoft.com/default...b;en-us;841249
http://support.microsoft.com/default...b;en-us;814130
Magy
"Joann" wrote:

> I have msde 2000 installed by one of my applications and
> every time i access my pc via Remote Desktop, the msde
> initializes? When I'm at my pc locally, it is not an
> issue.
> What is Remote Desktop doing that is causing this
> behaviour and how can i get it to stop?
> thanks in advance
> jo
>
>

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 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 question

Hi

I have a server with windows 2003 and SQL Server 2005 installed.

When I use the client on the server to connect to the database (using windows authentication) it does this no problem. And when remotely connecting (using SQL authentication) it 'seems' to do it no problem as well.

However in "SQL Sevrer Management Studio" on the server when I connect it displays the server name with a nice green circle with a little arrow inside. On the remote computer it 'seems' to connect but I don't get the green circle with the arrow next to the server (just get a white circle) - Hope this all makes sense.

I get the feeling I can 'see' the server but can't connect properly. Is this common behaviour (white circle) or have I made a 'school boy error' somewhere along the way!?

Any help/suggestions would be much appreciated. Thanks in advance!

Regards

Toby

Would you go to Surface Area Configuration to enable Remote Connection?|||

Surface Area Configuration is setup correctly (on the server):

Database Engine/Remote Connections => Local and Remote Connections - using both TCP/IP and named pipes.

|||

Just to clarify where I am and what I have tried

Basically on the remote computer I log in with "sa" and it says it connects, but it doesn't look/behave like its connected. And on the server I log in with windows authentication and it connects no problem with full functionality.

The server is setup to accept remote connections. It uses both windows and sql authentication. I have tried playing around with the TCP/IP port numbers. I've stopped the windows firewall in administrative tools/services on both the server and remote machine. Also added sql drivers under the ODBC Data Source Administrator for both the client & server.

I am pretty much out of ideas and the problem is really starting to bug me. I can't be the only person getting this bug/problem!?

Thanks in advance
Toby

remote connection problem with sql server express

Hi

have sql server express 2005 installed on a laptop running XP sp2 with IIS installed. installed sql express during the visual studio 2005 install

having problems with remote connection errors messages when using aspnet_regsql.exe as well as when trying to view pages with sql db involved. in most tests to fix this i disable my firewall, still getting error messages tho

sqlBrowser is running as is sqlexpress in services both on auto

in the surface area configuration tool, the remote connection is set to local and remote connections is using both tcp/ip and named pipes.

in sql config manager- shared memory, tcp/ip, and named pipes are enabled for both the server and the client, the order on the client is shared memory then tcp/ip then named pipes

inside the server's tcp/ip on the ip tab, IP1 is set to active, enabled, the ip address is 192.168.1.41 which is different from the laptop ip (192.168.1.39), there is no port number for IP1, should there be a port number? dynamic port is 0

IP2 is set to active, enable with address 127.0.0.1 and also does not have a port number, dynamic port is 0

IPAll is using dynamic port 1232 but also has no port number.

for the client TCP/IP setting the default port is 1433 and it is also enabled.

also downloaded and intalled SSMS-EE and under properties for the sql server >connection, the allow remote connection box is checked. I am using the default instance sqlexpress, there is another instance microsoftbcm (what is that?) and it also has the allow remote connection box checked.

server security is set to windows authentication

keep getting

clues appreciated, take care

What is the error message you are seeing?

|||

when i run aspnet_regsql and get to about the third page in the wizard and click on the dropdown for the database the error message i get is

Failed to query a list of database names from the SQL server

An error has occurred while establishing a conneciton to the server. when connecting to SQL server 2005, this failure may be casued the 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)

no databases show up at all in the wizard.

not sure why or if i should be using the named pipes protocol,

a blog said to run this command- SQLCMD -s .\sqlexpress not sure what this does so i haven't tried it yet as the article seem to be for VS beta august.

thanks for responding Rob

|||this worked for me with the same problem:

from:ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/udb9/html/914f7491-e2be-4b0d-b3aa-fe5409cdbafa.htm

---

By default, the default instance of Microsoft SQL Server Database Engine listens on named pipe \\.\pipe\sql\query. Named instances of SQL Server Database Engine and SQL Server Mobile listen on other pipes. Use SQL Server Configuration Manager to change the pipe used by the Database Engine.

There are three ways to connect to a specific named pipe with a client application:
Run the SQL Server Browser service on the server.|||

I'm having a similar problem (same error message) - and the above solution did not resolve it in my case. Does anyone else have any other ideas on this issue?

|||

OK, I think I have a solution to this one!

I did a little more research and came across this post on WebServerTalk.

This led me to the following post on MSDN's SQL Server Express blog:

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

The way I set mine up was to follow the instructions in Step 2, option A in the MSDN post, then change my connection string to the following:

connectionStr = "server=[MachineName],[PortNo]; data source=[MachineName]\SQLEXPRESS; user id=[SQLServerUser]; password=[password]; Initial Catalog=[dbName]"

Example:

Machine Name = MyMachine

Port Number (as specified in the SQL Server Config) = 2301

User Id = dbReader

Password = p@.ssword

Database Name = MyDB

connectionStr = "server=MyMachine,2301; data source=MyMachine\SQLEXPRESS; user id=dbReader; password=p@.ssword; Initial Catalog=MyDB"

This worked for me - hope this helps others.

P.S. Does anyone have an idea how to make this work with the Integrated Security parameter (i.e., using "Integrated Security=SSPI;" instead of "userid=bla; password=yak")? Could not get this to work using that parameter - had to identify a specific SQL Server Login.

|||

not sure i can even connect locally, ran the sqlcmd -s -e \sqlexpress in a dos box and get the error message below, also ched the registry and the setting are:

name SQLexpress -- type reg_sz -- data mssql.1
not sure if i have ever connected locally, have never been able to get either the sqlcmd -e -s \sqlexpress to work, or the aspnet_regsql wixard to work, (is ther anothe way to test the local connection?) for the sqlcmd i get a get a message like this,

HResult 0x274D, Level 16, State 1
TCP Provider: No connection could be made because the target machine actively refused it

sqlcmd: Error Microsoft sql native client :an error has occurred while establishing a connection th the server. When conecting th SQL server 2005, this failure may be caused by the fact that unde rth edefault settings sql server does not allow remote connecitons. and the a login timeout expired.

the server has a different ip address than the laptop itself, sql express is installed on the laptop.

the aspnet_regsql error message is very similar,

|||

The SQL Express Blog (entry above, but here it is again for good measure, http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx) desicribes how to configure SQL Express and your computer to accept remote connections.

In addition to the steps you've taken, you have to open an Exception in the firewall for both SQL Server and SQL Browser; many people miss this step. The blog describes how to do this for the Windows Firewall, the process will be similar for other firewalls.

Hope this helps,

Mike Wachal
SQL Express

|||

Linking to a related thread:

permissions sql express and remote connection problems TIA

The issue in the above thread was that -s was used instead of -S for sqlcmd. The proper argument for the server name is -S with capital S.

Thanks
Laurentiu

|||Integrated security = SSPI works in my case because I set the login name into windows authentication instead of sql server authentication and give specific roles.|||

CSharpener,

I just had to post a big THANK YOU for taking the time to post what fixed it for you because it was also the solution for my problem. I'm running Windows Server Small Business Server and I was starting to think that maybe the Microsoft SQL Server Desktop Engines for Reporting and Sharepoint had something to do with my problem. So I've spent the past hour and a half crawling the web looking through all sorts of posts related to this subject and most of them had the same solution:
Enable Named Pipes in the SQL Server Configuration Manager. I did this about 4 different times just to make sure I was not missing anything. However it did not solve the issue.

My SQL instance is not default, it is named so once I removed the instance name from the pipe name I was able to get the ASPNET_REGSQL to find my database.

Going from:
\\.\pipe\MSSQL$MYSQLSERVERNAMEWASHERE\sql\query

To:
\\.\pipe\sql\query

Worked like a charm. Thanks for passing it along instead of just moving on to the next problem with the information hoarded in your head. :)

DR

|||An easy-to-miss behavior of the aspnet_regsql wizard (or command aspnet_regsql -W), is that the Server Textbox is pre-populated with your machine name. That will cause the wizard, in your case, to fail on the third page. You need to change the Server Textbox.Text from [MachineName] to [MachineName]\SQLEXPRESS.

That should help, no matter what protocols you have enabled.
|||

I am new to SQL 2005 and Visual Studio 2005. I used the following Connection string as given in a Visual Studio 2005 sample program.

ConnectionStr= "Server=(local);DataBase=;Integrated Security=SSPI"

If there are more than one SQL Server 2005 instance ( I only have SQL 2005 Express to test) even if I fixed the Remote Connections, firewall and Browser services I still get the error:

An error has occurred while establishing a conneciton to the server. when connecting to SQL server 2005, this failure may be casued the 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)

Now I found the solution for this problem using integrated security parameter. I can connect to SQLExpress instance using the following string.

ConnectionStr = "Server=.\SQLExpress;DataBase=;Integrated Security=SSPI"

|||YES! YES! YES! Thanks for your post implemental.com. After reading through all of the above posts and getting pretty frustrated, I tried adding the SQLEXPRESS string to the server name as you suggest and everything suddenly worked! Thanks for your posting.|||

thanks! guys