how to change the setting in sql server 2005 embedded edition

Posted on 2009-04-19
Medium Priority
Last Modified: 2012-05-06
I have a website on pc A  which is only installed wss3.0 ,just has sql server 2005 embedded edition combined with wss3.0
Now I want to move the whole website and database to pc B which is installed wss3.0 and sql server2005.  I opened sql server management studio and tried to connect pc A database, it said
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
I think the sql server in pc A may not allow remote connection, but it's an embedded edition, there is no configuration tools I can change the setting.
Does anyone know how to change the remote connetion setting in sql server 2005 embedded edition?
Question by:CRIIT
  • 3
  • 2
  • 2
  • +1
LVL 13

Expert Comment

ID: 24182571
On PC A; You should have a SQL Server Surface Area Configuration tool; In there; cant remember which option; but there is one to allow remote connections.

Alternatively; Go into SQL Server Configuration from Start, Progs, MSSQL and enable TCP/IP.

LVL 12

Expert Comment

by:Chris M
ID: 24184114
Hi go here:
Start > Programs > SQL Server 2005 > Configuration tools > SQL server Surface Area Configuration.
Under the section "Configure surface area for local host", click the first option which is "Surface Area Configuration For services and Connections".
Choose the instance name you want to configure by expanding it, click "Database Engine", then click "Remote Connections".
Choose the option Local and remote connections. You may choose TCP only (recommended) or use TCP and named pipes.
Now go to SQL server Configuration manager to get the instance's port number which you will use to connect from the remote host.
Click Start > Programs > SQL Server 2005 > Configuration tools > SQL server Configuration Manager
Under SQl server 2005 Network Configuration, highlight protocols for the instance you just configured.
Go to TCP/IP properties, right click and choose properties, then go to IP addresses tab and scroll down for the port.
This will give you all the network configurations you need.
All the best,
Chris Musasizi.
LVL 12

Expert Comment

by:Chris M
ID: 24184132
Sorry i forgot to say that connections to your instance are failing because you do not allow remote connections so enabling them will definitely allow your other server access your SQL instance.
Secondly, you will need to specify the port in the TCP connections in case your SQL instance does not run on the default port.
All the best ....
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 24185507
Thank you
But I dont't have sql server suface configuration area as I just installed wss3.0 on pcA, and I just saw sql server 2005 embedded service running in the services. There is no any icon I can click to access the sql server.You can see my desktop in the attached picture
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24186525
Simply put you cannot enable remote access using that edition.  It is by design.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24186610
Having said that this guy claims the following workaround (I will be impressed if it works for you):

Author Comment

ID: 24187380
I tried before. That case is sql server embedded endition and sql server 2005 are installed on the same machine. If you open the sql server management studio, type \\.\pipe\mssql$microsoft##ssee\sql\query in the database server' name, you can connect the embedded edition from 2005.

But now the condition is I want to remote Sql server embedded edition database of A from B using sql server 2005, in the dataserver' name, it' swrong to type \\.\pipe\mssql$microsoft##ssee\sql\query
as they are different machine. So I am wondering is there other style name like \\.\pipe\mssql$microsoft##ssee\sql\query  I can type in B ?

LVL 75

Accepted Solution

Anthony Perkins earned 1500 total points
ID: 24190344
Than as I mentioned in my first comment you amy be up the proverbial creek.  That edition was never intended to be used with remote access.

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question