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

Remote(over a network) Database Connection Issue

This has to be an easy question.

WORK2 - My workstation (xp pro)
WORK3 - Location of SQL Express 2005

WORK3\COMPANY
sa,password

WORK3 is a XPPro, both WORK2 and 3 have full admin rights to their machines and the network, I am using SQL Authentication (sa, password)
I have used the SQL Server Config app to enable the 4 Protocols, then restarted service. I can connect locally to my database via "SQL Server Management Studio Express", and through my VB application. I have checked "allow remote connections" in the database properties.
I do not know what i am missing? any thoughts?

Thanks in Advance

Are

you using a connection string? which is?|||

Are you using a connection string? which is?

|||

My VB6 connectionstring is:

This works PERFECT when the data souce is a local SQL Express Database

mstrProvider = "SQLNCLI.1" 'SQL Server 2005 Express

mstrPassword ="password"

mstrDataSource =<my database name>

goSQLDataSource= "WORK/SQLSERVER"

ConnectString = "Provider=" & mstrProvider & ";" & _
"Persist Security Info= False" & ";" & _
"Integrated Security=SSPI" & ";" & _
"User ID=sa" & ";" & _
"Password=" & mstrPassword & ";" & _
"Initial Catalog=" & mstrDataSource & ";" & _
"Data Source=" & goSQLDataSource

When i use the SS Management Studio i cant connect to a remote SQL Server even though the allow remote connection is enbled.

|||

How is your authentication mode? if you are using a sql logins like a "sa", the authentication mode must be "sql server and windows authen....."

|||

are you refering to this?

"Integrated Security=SSPI" & ";" & _

I am always going to use SQL Authentication...Local or remote.

When I use SSMS i have to change it from Windows Auth to SQL b/c it doesnt let you enter a password if you are using Windows.

Does that answer your question?

|||

Sorry, when I told verify the authentication mode I mean on the SQLserver configuration properties.

|||

Verify the authentication mode on <INSTANCE>\properties\security\server authentication...

|||

The option button is select for "SQL Server and Windows Authentication mode" under the Server Properties/security

|||Why does Microsoft have to be so difficult? lol...sorry just venting|||

Verify this other one option <INSTANCE>\properties\connections\"alow remote connection s to this server"

|||

Maximum number of concurrent connections=0

Use query gov. = unchecked

default connection options= all unchecked

allow remote connections to this server=checked

remote query timeout=0

require distrubited transactions=unchecked

Configured values=selected

sql

remote server connection problems

Having all kinds of problems trying to establish a connection between a VB5 application and a remote server Sql Express database. The connection works fine with a user instance on my local machine. At one time I did have the remote connection working successfully using Sql Server authentication, whereby the application was passing the login and password through the connection string as so:

Provider=SQLNCLI.1;Persist Security Info=False;Data Source=<server name>\sqlexpress;Database=<database name>;Uid=<userid>;Pwd=<password>;

I've been away from using this development database for several weeks. Things are little foggy from that initial effort, and I recently tried putting up a newer copy of the development database on this server from my local machine copy. That local database copy apparently didn't have authentication login/user used by the app, so I created the Login account and assigned the default database, then created the User account, referenced it to the Login account, and gave the user account a role as db_owner. Now when my app attempts to connect with aforementioned connect string, I get an error "Login failed for '< userid>'. I've redone this thing a hundred times and I still get this error. I did put a Windows Update on this server recently so don't know if that's an issue.

I could try using Windows authentication mode, but am not sure how to establish Logins/Users under this mode. Mainly, I'm don't know what the name of these accounts should be. Do you set the Windows login name up in both Login and User accounts? Below is a modified connect string I'd probably use for this mode:

Provider=SQLNCLI.1;Persist Security Info=False;Integrated Security=SSPI;Data Source=<servername>\sqlexpress;Database=<database name>;

Any ideas or suggestion on things to try on either of these modes? At my wits on this and am up against the clock.

TIA ... Rick

If you're using SQL Authentication, the error would suggest that the UserID you're passing in your connection string either doesn't exist as a Login on the Server or that the Login does exist, but it isn't associated with a specific database User. You could probably find out the exact cause by looking in the error log, where it will list not only the error message, but the specific State. If you're getting the 18456 login error, the meaning of the common states can be found in this blog entry.

If you haven't read the Books Online topics covering security already, you should stop what your doing and go read them. No explaination provided in this forum is a subsitute for an actual understanding of the technology.

Regardless of the type of authentication you use, you need both a Login and a User in order to do anything in a database. The Login is what gives you access to the Server while the User is what gives you permission in the database. In Windows Auth, the Login is always associated with either a Machine or Domain User or Group, while SQL Auth Logins can just be any arbitrary name you want to give it. The Database user can be named pretty much anything you want to call it, you make the association between a Login and a User either using Management Studio UI or T-SQL commands. Here is a Windows Auth example:

Login - Domain\Rick (A Windows domain user)

Database User - Rick

You would associate the Domain\Rick Login with the Database User named 'Rick'. You would assign specific permissions in your database to the user named Rick, not to the Login. To be clear, I could have named the user Mike and it wouldn't make a difference. The Database User is totally separate from the Login and from the Windows Users in your domain. This is all explained in Books Online.

Mike

|||Hey Mike,

Mike Wachal - MSFT wrote:

If you're using SQL Authentication, the error would suggest that the UserID you're passing in your connection string either doesn't exist as a Login on the Server or that the Login does exist, but it isn't associated with a specific database User.

Neither of these is true. The Login account exist, default database selected as application database, password set, User account created that points to Login account of the same name, role given as db_owner. It should work. It does not.

You could probably find out the exact cause by looking in the error log, where it will list not only the error message, but the specific State. If you're getting the 18456 login error, the meaning of the common states can be found in this blog entry.[

It goes back and forth haphazardly between state 16 and state 8. Now it's consistently state 16 with a "cannot open database" error. What other permissions are there to give to this thing?

In another connectivity test, there was another oddity. Using Crystal Reports XI, I attempted to create a connection using OLE DB ADO. Selected "Provider for Sql Server" (note: couldn't get a connection out of their Sql Native Client option), logged into the database as SA. However, the application tables belonging to the database were not visible. Everything should be visble and available to SA, right?

Here's what I've been doing to move a copy of the local database up to a development test server, using SqlExpress Manager:

- Detach local db
- Detatch server db and deleted existing MDF/LDF pair since I'm going to overwrite these.
- Copy MDF/LDF pair up to server
- Attach pair to server
- Rename database, removing full pathname from database name.
- Create Login account used by app as Sql Server authentication and establish default db for app. Note, this login seems to persist each time I perform this sequence. Doesn't seem to make a difference if I use what's there, or delete it and start fresh.
- Create User account of same name and point Login Account. Assign role as db_owner.
- Reattach app database on local machine to it's available there again.

This should work, and I had it working once before. Windows authentication mode isn't allowing a connection either. As it is, I've spent a few days on this, am frustrated, and about ready to give up on SqlExpress and move on MySql.

Supplement: I just uninstalled and reinstalled SqlExpress and Sql Native Client on the server. I didn't remove any of the extra tools and such. After successful installation I was going to reconfigure for remote access, however, all my settings were still configure for remote access. I restarted the service(s), including Sql Browser. Ran the process of moving a copy of the database over to server (again), attached, and ... same old error ... "cannot open database", both with Sql and Windows authentication.

Rick

|||Mike,

Well, after three days of troubleshooting, the problem turned out to be that the database name in the connection string must explicitly reference the .MDF extension. For example...

Problematic connect string:
connection=Provider=SQLNCLI.1;Persist Security Info=False;Data Source=<server name>\sqlexpress;Database=myDatabase

Working connection string

connection=Provider=SQLNCLI.1;Persist Security Info=False;Data Source=<server name>\sqlexpress;Database=myDatabase.mdf

To the best of my recollection, Sql Server doesn't normally require an extension, but apparently Sql Express now does.

Rick|||

SQL Express does not require the extention, but it does require the correct name. SQL Server will also keep a hold of the name of a database and tie it to a specific file location, even after detach. Database names can be pretty much anything, including a full path to a file, in fact, when you use AttachDbFilename and don't specificy an explicit name, your database will actually be given an "auto name" that is the path to the file.

It appears that at some point in the past, the file managed to get named as the file name with extenstion rather than just the base file without extention. Since that informaiton is cached in some form in the Master database, the next time you attached a database from the same path, it "picks up" to name from the previous attach. This isn't unique to SQL Express, any copy of SQL Server could do this.

If you look in Management Studio at the server, you should find the database name listed as myDatabase.mdf. You should be able to change this if you want by just renaming the database.

Mike

|||Yeah, I thought about it some more overnight. When moving the local copy up to server and renaming the file (removing the full path from the name), I didn't remove the .MDF extension. So, just have to be mindful of this in future when attaching any database to the server.

Remote database Connection Error!

Hi,
My Application which is written in VB.Net uses database connection to my
Local Instance of SQL Server Express version.
My connection string is " Data
Source=SM5\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\MgmtSystem.mdf;Initial Catalog=MgmtSystem;Persist
Security Info=True;User ID=sa;Password=belmont". When I run my application
from the same machine where SQL Server instance is installed, it works fine.
But now I am intending to use it as Client-Server type as database on Server
and application running from another machine. To test this I have deployed
my application on another machine. When I run it I get following error, "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 setting SQL Server does not allow remote
connections.(provider: SQL Network Interface, error: 26 - Error Locating
Server/Instance specified)(Microsoft SQL Server Error:-1).
To Fix this I did try enabling all protocols(Shared Memory, TCP/IP, Named
Pipe). Then I even started SQL Browser service to enable the instance to be
browsed by client. Then Checked if Hide Instance property. Still no Luck.
I am really going crazy. This is really important for me since we are
migrating from Access to SQL Server Express. Since in Access my application
can have client server
Architecture. Same thing I need to have in SQL Express.
Any help is really appreciated.
Thanks,
Sujatause sql2k5 surface area configuration tool to enable 'remote connections'.
--
-oj
"Sujata P" <SujataP@.discussions.microsoft.com> wrote in message
news:94D32EEB-9098-4A51-B55D-95BD84B5AA96@.microsoft.com...
> Hi,
> My Application which is written in VB.Net uses database connection to my
> Local Instance of SQL Server Express version.
> My connection string is " Data
> Source=SM5\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\MgmtSystem.mdf;Initial
> Catalog=MgmtSystem;Persist
> Security Info=True;User ID=sa;Password=belmont". When I run my application
> from the same machine where SQL Server instance is installed, it works
> fine.
> But now I am intending to use it as Client-Server type as database on
> Server
> and application running from another machine. To test this I have
> deployed
> my application on another machine. When I run it I get following error,
> "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 setting SQL Server does not allow remote
> connections.(provider: SQL Network Interface, error: 26 - Error Locating
> Server/Instance specified)(Microsoft SQL Server Error:-1).
> To Fix this I did try enabling all protocols(Shared Memory, TCP/IP, Named
> Pipe). Then I even started SQL Browser service to enable the instance to
> be
> browsed by client. Then Checked if Hide Instance property. Still no Luck.
> I am really going crazy. This is really important for me since we are
> migrating from Access to SQL Server Express. Since in Access my
> application
> can have client server
> Architecture. Same thing I need to have in SQL Express.
> Any help is really appreciated.
> Thanks,
> Sujata|||Thanks oj...but I found answer...Actually I did everything last time except
creating exceptions in Windows Firewall for instance of my SQL Server to
unblock the connection.
For anyone who is facing similar problem, Follow
http://support.microsoft.com/?id=914277..it is very useful for creating
remote connection.
"oj" wrote:
> use sql2k5 surface area configuration tool to enable 'remote connections'.
> --
> -oj
>
> "Sujata P" <SujataP@.discussions.microsoft.com> wrote in message
> news:94D32EEB-9098-4A51-B55D-95BD84B5AA96@.microsoft.com...
> > Hi,
> > My Application which is written in VB.Net uses database connection to my
> > Local Instance of SQL Server Express version.
> > My connection string is " Data
> > Source=SM5\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL
> > Server\MSSQL.1\MSSQL\Data\MgmtSystem.mdf;Initial
> > Catalog=MgmtSystem;Persist
> > Security Info=True;User ID=sa;Password=belmont". When I run my application
> > from the same machine where SQL Server instance is installed, it works
> > fine.
> > But now I am intending to use it as Client-Server type as database on
> > Server
> > and application running from another machine. To test this I have
> > deployed
> > my application on another machine. When I run it I get following error,
> > "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 setting SQL Server does not allow remote
> > connections.(provider: SQL Network Interface, error: 26 - Error Locating
> > Server/Instance specified)(Microsoft SQL Server Error:-1).
> >
> > To Fix this I did try enabling all protocols(Shared Memory, TCP/IP, Named
> > Pipe). Then I even started SQL Browser service to enable the instance to
> > be
> > browsed by client. Then Checked if Hide Instance property. Still no Luck.
> > I am really going crazy. This is really important for me since we are
> > migrating from Access to SQL Server Express. Since in Access my
> > application
> > can have client server
> > Architecture. Same thing I need to have in SQL Express.
> >
> > Any help is really appreciated.
> >
> > Thanks,
> > Sujata
>
>

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

remote connection to 2005 express: Login Failed?

I followed the steps outlined in a blog I found to setup SS2005 express for remote connections. Everything appeared to go smooth until I actually tried to logon ;)

I have Sql Server 2005 Express installed on my WinXP PRO machine at home (no domain). I have opened the ports on the firewall, etc.

When I try and login from the SQLCMD command line tool I get a Login Failed error. Here is the connection string that I'm trying to use:
SQLCMD -S **.***.**.*\SQLEXPRESS -U Steve -P MyPassword

The error:
Login failed for user 'Steve'.

When I use Management Studio Express on the same machine running Sql Server, it shows the User name: NEVERSTILL\Steve

'NEVERSTILL' is the name of my computer.

If I try and use that same username with a remote connection I get the same error.

I have googled for the error and msg code, but nothing fitting my situation is coming up.

Anyone have any ideas for me?

Check the error log and please post the error state that shows there before the login failed message.

Thanks
Laurentiu

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

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

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