in SQL 2005 in the Management Studio when I right click a table and script to new window the output includes the Identifier delimiter i,e. [FIELD_NAME] I would like these off by default.. for the life of me I can't find out if or where this is done...
Can anyone help please?
Best Regards
Jon
Hi everybody:
I am working on a query that referrences a table in a remote database. It seems in Management Studio 2005 anytime you open a new query window you should connect to a specific database instance that's why when I refer to the remote database table using Fully Qualified Name it tells me the database name is unknown.
Do you have any solutions for this?
Thanks a lot
Which qualified name did you use ?
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
[server].[database].[owner].[table]
|||Hi,if you connect to a database / server you can reach any (linked) server and therefore remote database that are setup on the server machine. You don′t need to specify the remoteserver at connection time, that why you can specify it the four part name:
[server].[database].[owner].[table]
But the [server] has to be a linked server. If you don′t know how to setup, look in the BOl there are some good straight forward examples for it.HTH, jens Suessmeyer.
http//www.sqlserver2005.de
|||
You can try with OPENROWSET or OPENQUERY function. To do this you need to enable this features from "SQL Server Surface Area Configuration" - Ad Hoc Remote Queries (you need to check "Enable OPENROWSET and OPENDATASOURCE). After you enable this option you can use this function to retrieve data from another server or to get data from another format (including MS Excel - for example).
SELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;', 'SELECT GroupName, Name, DepartmentID FROM AdventureWorks.HumanResources.Department ORDER BY GroupName, Name') AS a;We can connect to everything else except SSIS. When we try to connect to SSIS, it says:
Cannot Connecto to B
Additional Information:
Failed to retrieve data fro this request. (Microsoft.SqlServer.SmoEnum)
Conecct to SSIS Server on machine "B" failed:
The RPC server is unavailable
.
Conecct to SSIS Server on machine "B" failed:
The RPC server is unavailable
from connection dialog on A, we have tried putting FDQN of B.
we are using windows authentication with domain account.
per one of the suggestions, we modified MsDtsSrvr.ini.xml to change the ServerName for MSDB to be one fo the following:
B, .\MSSQLSERVER, B\MSSQLSERVER, .
we also went to DCOM settings of MsDtsServer, under security, Customize for selected for all three Permissions, and under customize settings, B\Administrators was given full access for everything.
also the domain user was added to both machines in the administrators group.
i'll appreciate any ideas.Might there be a firewall enabled on machine B?|||Both are windows 2003 boxes with no firewalls. there is a firewall in between box b and a, and we requested hosting services to open ports 135, and 3882 which they did. we have emailed them back today to make sure that the ports were indeed opened.
btw, i am with mcs. i posted it here since i wasnt sure who to direct this question to, since i did not have any internal BI contacts. i am working on this issue with a client.
Thanks
Ali|||
We are using DCOM, not RPC and port 135 may not be enough.
This port serves as RPC endpoint for initial service discovery, but then the client should be able to communicate via (randomly assigned) port used by MsDtsSrvr.exe
|||If it always picks a random port, do we have to resort to what is specified in this article (static end points for DCOM ) http://weblogs.asp.net/rhurlbut/archive/2004/03/07/85542.aspx so we can open only 2 ports (or a range of ports in the firewall) ?
|||Or, can we configure a static endpoint for the MsDtsServer.exe from the application properties under component services? is that same as setting the registry to configure port settings?Thanks
Ali|||
Yes, it creates the same registry key.
|||I have the same problem. Did you find a resolution ? I can connect from my desktop to Analysis Services and Database Engine, just not to SSIS. I haven't seen a useful answer to this anywhere !!!|||Hi,
I dont know exactly why this error comes but i solved it by removing the server name from the named pipes.
\\.\pipe\sql\query and entered this value in the named pipes field.
and i started working.
from
sufian
We can connect to everything else except SSIS. When we try to connect to SSIS, it says:
Cannot Connecto to B
Additional Information:
Failed to retrieve data fro this request. (Microsoft.SqlServer.SmoEnum)
Conecct to SSIS Server on machine "B" failed:
The RPC server is unavailable
.
Conecct to SSIS Server on machine "B" failed:
The RPC server is unavailable
from connection dialog on A, we have tried putting FDQN of B.
we are using windows authentication with domain account.
per one of the suggestions, we modified MsDtsSrvr.ini.xml to change the ServerName for MSDB to be one fo the following:
B, .\MSSQLSERVER, B\MSSQLSERVER, .
we also went to DCOM settings of MsDtsServer, under security, Customize for selected for all three Permissions, and under customize settings, B\Administrators was given full access for everything.
also the domain user was added to both machines in the administrators group.
i'll appreciate any ideas.Might there be a firewall enabled on machine B?|||Both are windows 2003 boxes with no firewalls. there is a firewall in between box b and a, and we requested hosting services to open ports 135, and 3882 which they did. we have emailed them back today to make sure that the ports were indeed opened.
btw, i am with mcs. i posted it here since i wasnt sure who to direct this question to, since i did not have any internal BI contacts. i am working on this issue with a client.
Thanks
Ali|||
We are using DCOM, not RPC and port 135 may not be enough.
This port serves as RPC endpoint for initial service discovery, but then the client should be able to communicate via (randomly assigned) port used by MsDtsSrvr.exe
|||If it always picks a random port, do we have to resort to what is specified in this article (static end points for DCOM ) http://weblogs.asp.net/rhurlbut/archive/2004/03/07/85542.aspx so we can open only 2 ports (or a range of ports in the firewall) ?
|||Or, can we configure a static endpoint for the MsDtsServer.exe from the application properties under component services? is that same as setting the registry to configure port settings?Thanks
Ali|||
Yes, it creates the same registry key.
|||I have the same problem. Did you find a resolution ? I can connect from my desktop to Analysis Services and Database Engine, just not to SSIS. I haven't seen a useful answer to this anywhere !!!|||Hi,
I dont know exactly why this error comes but i solved it by removing the server name from the named pipes.
\\.\pipe\sql\query and entered this value in the named pipes field.
and i started working.
from
sufian
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 you
Hello everyone, I am trying to connect to a remote SQL 2005 database from Microsoft SQL Server Management Studio.
I enabled the remote connection on the server and creating a user on the server.
I am trying to connect to the server from my workstation using sql server authentication with the login name already created on the server.
I am receiving this error: cannot connect to "server name"
cannot open user default database. Login failed.
Login failed for user "username".(Microsoft SQL Server, Error: 4064)
Can anyone help?
under your server registration properties... do you have authentication set to "SQL Server Authentication"?
You aren't behind a firewall that blocks connections to your remote? (they do this on corporate networks)...
Hello everyone, I am trying to connect to a remote SQL 2005 database from Microsoft SQL Server Management Studio.
I enabled the remote connection on the server and creating a user on the server.
I am trying to connect to the server from my workstation using sql server authentication with the login name already created on the server.
I am receiving this error: cannot connect to "server name"
cannot open user default database. Login failed.
Login failed for user "username".(Microsoft SQL Server, Error: 4064)
Can you help?
Whilst you created an account on the machine, have you granted that account access to its default database which hopefully is your application database?
Also, you can investigate what the connection string should be like:
1) Create an empty text file in windows explorer and rename it to X.UDL
2) Double click on it and the datalink provider dialog will appear.
3) Select the provider tab. Find the provider for SQL Server express and click next.
4) Select your database and select integerated authentication.
5) Test the connection and save it.
6) Compare the contents of X.UDL with your connections string.
You could try looking on http://www.connectionstrings.com/ for examples of connection strings.
HTH
I'm trying to remotely create a maintenance plan using the management studio in order to backup databases. I get the following error.
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
TITLE: Maintenance Plan Wizard Progress
Create maintenance plan failed.
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'. (Microsoft.SqlServer.MaintenancePlanTasks)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The specified '@.subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The server doesn't have the Management studio client installed so I can't create the maintenance plan locally. If I install just the Management studio will the data currently in the server be effected? Any solution to the remote problem?
Thanks,
Neil
SSIS(SQL Server integration services) need to be installed along with the server, for executing maintenace plans successfully.
For SP1, the current plan is either SSIS or tools installed on the server box would address this issue.
Gops Dwarak
I'm trying to remotely create a maintenance plan using the management studio in order to backup databases. I get the following error.
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
TITLE: Maintenance Plan Wizard Progress
Create maintenance plan failed.
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'. (Microsoft.SqlServer.MaintenancePlanTasks)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The specified '@.subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The server doesn't have the Management studio client installed so I can't create the maintenance plan locally. If I install just the Management studio will the data currently in the server be effected? Any solution to the remote problem?
Thanks,
Neil
SSIS(SQL Server integration services) need to be installed along with the server, for executing maintenace plans successfully.
For SP1, the current plan is either SSIS or tools installed on the server box would address this issue.
Gops Dwarak
I'm new to SSIS and I'm having problems getting a remote connection to the SSIS service using Management Studio on my workstation. If I terminal Service onto the Server I have no problems connecting to SSIS, but if I try to connect remotely I get the "Access is denied" error message. I have completed the following steps:
To configure rights for remote users on Windows Server 2003 or Windows XP
1. If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.
2. Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.
3. Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.
4. Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.
5. Right-click on MsDtsServer and select Properties.
6. In the MsDtsServer Properties dialog box, select the Security tab.
7. Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.
8. In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.
9. Click OK to close the dialog box. Close the MMC snap-in.
10. Restart the Integration Services service.
But I still get the Access is denied error from my workstation?
I have Power User rights on the server and I'm a sysadmin in the database instance. The SSIS packages I am trying to access are stored in the database. If I add myself to the local administrators group on the server I CAN get remote access, but this is not an acceptable solution in our production environment.
Thanks for any help
Please search the forums... You'd find your answer there, however:http://www.ssistalk.com/2007/04/13/ssis-access-is-denied-when-connection-to-remote-ssis-service/|||Thank you
Hello.
I am trying to remotely connect to SSIS from my PC using windows authentication in SQL mgmt studio and I keep getting the following error message:
Cannot connect to <server>
Additional Information:
Failed to retrieve date for this request. (Microsoft.SqlServer.SmoEnum)
Connect to SSIS service on machine "<server>" failed: The RPC server is unavailable.
I do not get the same error when I connect to the database engine, just SSIS. I don't have a firewall inbetween the machines either so it can't be that.
Has anyone else had a similar problem? if so, I would be grateful if you can you tell me how you got around it
Thanks
See this thread where I answered a similar question regarding not being able to connect to SSIS from Management Studio, I'm assuming this may be your problem:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=254204&SiteID=1
HTH
|||I get the same RPC error, but one of my co-workers doesn't get the error, and he can connect to SSIS from his desktop, but I cannot. I assume I don't need to change the .i' GAD 0l3/2e3/2 006o Rn the server since he has no problem connecting.
Any other ideas ?
Hello.
I am trying to remotely connect to SSIS from my PC using windows authentication in SQL mgmt studio and I keep getting the following error message:
Cannot connect to <server>
Additional Information:
Failed to retrieve date for this request. (Microsoft.SqlServer.SmoEnum)
Connect to SSIS service on machine "<server>" failed: The RPC server is unavailable.
I do not get the same error when I connect to the database engine, just SSIS. I don't have a firewall inbetween the machines either so it can't be that.
Has anyone else had a similar problem? if so, I would be grateful if you can you tell me how you got around it
Thanks
See this thread where I answered a similar question regarding not being able to connect to SSIS from Management Studio, I'm assuming this may be your problem:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=254204&SiteID=1
HTH
|||I get the same RPC error, but one of my co-workers doesn't get the error, and he can connect to SSIS from his desktop, but I cannot. I assume I don't need to change the .i' GAD 0l3/2e3/2 006o Rn the server since he has no problem connecting.
Any other ideas ?
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
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
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
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
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
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
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
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
