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
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
No comments:
Post a Comment