Showing posts with label visual. Show all posts
Showing posts with label visual. Show all posts

Wednesday, March 21, 2012

Remote SQL Server Connect using VB Studio?

I've read all of the related questions and none answer my situation. I am using Visual Studio 2005. I have not been able to connect to the remote SQL database at all. I have attempted to write a SMO to make the connection however, the info I have been able to get to on MSDN didn’t have enough information to carry me through.

Here is the code I found it on MSDN and have been attempting to use.

Module Module1

Sub Main()

Imports Microsoft.SqlServer.Management.Smo

Imports Microsoft.SqlServer.Management.Common

'Declare a ServerConnection object variable to specify SQL authentication, login and password.

Dim conn As New ServerConnection

conn.LoginSecure = False

conn.Login = xxxx

conn.Password = xxxxx

'Connect to the local, default instance of SQL Server.

Dim srv As Server

srv = New Server(conn)

'The actual connection is made when a property is retrieved.

Console.WriteLine(srv.Information.Version)

'The connection is automatically disconnected when the Server variable goes out of scope.

End Sub

End Module

The code executes however, nothing happens. I don’t know where the IP address comes into play. Thanks in advance.

· SQL Server 2005 Standard

· TCPIP

· Server not starting (I’m able to connect using SQL Server Management Studio

· I don’t know whether SQL Browser is enabled enabled. I use the IP address when using SQL Server Management Studio

· I’m not sure however, I believe it runs under Network Service with the Host provider

· I have not made a firewall exception

· Windows XPSP2

· Running Norton AntiVirus 2007

Thanks for any help or advice.

The conn object will takt the ip address if you want to connect with one of that to the server, then you probably do not nee the login and the password specified in the code as you can pass it with the connection string. (See connectionstrings.com for more information) If you want to specify the login and password within your application you can use the new Server("10.10.10.10") (as 10.10.10.10 is the IPAddress)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thank youSmilesql

Saturday, February 25, 2012

Remote Connection to SQL Server 2005 over Windows Server 2003

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

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

Remote Connection to SQL Server 2005 over Windows Server 2003

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

Remote connection to 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

Monday, February 20, 2012

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

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

Remote Connection Fails with Web Service

Hello-
I am having a problem with a Web Service written in Visual Studio 2005.
I have a typed dataset that I created using the Visual Studio 2005
Data Source tool. This retrieves its data from a SQL Server 2000
database. My client calls a web service which calls a business
component to retrieve the typed dataset (i.e. Client --> Web Service
--> Business Component).
Whenever I put the web service between my client and my business
component I get the following error:
Server was unable to process request. --> An error has occurred while
establishing a connection to the server. When connecting to SQL Server
2005, this failure may be caused by the fact that under the default
settings SQL Server does not allow remote connections. (provider: Named
Pipes Provider, error: 40 - Could not open a connection to SQL Server)
If I do not put my web service in between my client and business
component, I do not get this error (i.e. Client --> Business
Component).
I have made sure that my SQL 2005 has Remote Connections enabled with
both Named Pipes and TCP/IP.
I don't understand why putting a web service between my client and
business component is making this error appear or how to remedy the
issue. Any help would be much appreciated!
-DrewI found the problem. In my web.config I had to add the following
configuration: <identity impersonate="true" />.
-Drew

Remote connection error - but everything installed locally?

I have a visual basic 2005 application where I am storing some data in a Sequel Server Database. It's my first attempt using SQL Server. It was developed on my machine and the database is on my local hard drive.

It was put onto an industrial PC and shipped last December (the vb app is an HMI).

Our service guy is installing the machine and asked for some changes to the VB screens. I ran my app (on my computer) and it crashed when I selected the screen where I use the db.

So I decided to look at the database using Visual Basic and I get the following error:

Microsoft Visual Basic 2005 Express Edition

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

I then tried to create a new database via Visual Basic and get the same error. My boss has it installed on his machine. Same error there. These are both machines that had no problem back in December!

I also have a industrial PC on my desk to test my app and it STILL WORKS there. That one though is not connected to the internet and hasn't gotten any updates or changes or whatever since I last had it on in December. I don't know if it was a windows update or a network change? But as I said, I'm not connecting remotely - SQL Server and the db are on our local machines.

Interesting enough, if I go to the control panel | Administrative Tools | Services, SQL Server (SQLEXPRESS) is set to automatic and is NOT started. I attempt to start it and get an error

Services

Windows could not start the SQL Server (SQLEXPRESS) on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 3417.

I am lost.

Thanks in advance for any help!

The concensus seems to be that this is related to compressed folders. Check out the posting here to check out where I got this crazy idea and the solutions that worked for others. I would also suggest checking out the log as the error recommends if you haven't already. Often the log contains more information that the error message. (Other times, it doesn't.)

Mike

|||

Thanks Mike!

There were several solutions listed at the link you provided. I tried in order:

1) compressed folder/files. I went to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data, right-clicked on each file. "Compress contents" was NOT checked. However I unchecked "ready to archive". I got a different error (instead of 26 it was 40) when I tried to create a new database.

2) I ran SQLdiag found in C:\Program Files\Microsoft SQL Server\90\Tools\Binn. It ran. There didn't seem to be a difference.

3) I right-clicked on SQLExpress in Services and changed the Log On to "Local System Account" with the box check. That did it for me.

Interesting enough, the industrial PC I have on my desk works and still has the Log On as "This Account" - but that's not connected to our server with any of our group policy stuff.

THANKS AGAIN Mike! I really appreciated your help.