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;The app is a project management tool for a construction company. Users in
the field will need to do remote updates both receiving and submitting data
back to the main office. I can't just make it a live web app because the
users will have to be able to work off line and sync data later (presumably
with a VPN connection).
What is the best way to go about this? I planned on using MSDE 2000 w/Access
front end. I will install MSDE on each laptop but what is the safest/most
reliable way to sync only that information which pertains to a specific user
along with other general information.
Any books that helpful with this specific issue? Any one have reusable
modules/code they'd be willing to send me?
TIA,
--JakeCXonsider replication, transactional or merge. Transactional is the
easier of the tow options but does mean that oly one side can make
changes to the data. With merge both sides cna change the data and you
create a set of rules to decide whic update is accepted.
Jake Jessup wrote:
> I am just beginning development of a new app and I need a little advice on
> the best way to go about doing something.
> The app is a project management tool for a construction company. Users in
> the field will need to do remote updates both receiving and submitting data
> back to the main office. I can't just make it a live web app because the
> users will have to be able to work off line and sync data later (presumably
> with a VPN connection).
> What is the best way to go about this? I planned on using MSDE 2000 w/Access
> front end. I will install MSDE on each laptop but what is the safest/most
> reliable way to sync only that information which pertains to a specific user
> along with other general information.
> Any books that helpful with this specific issue? Any one have reusable
> modules/code they'd be willing to send me?
> TIA,
> --Jake
>
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
Hi, I am trying to enable remote access on a SQL Server 2005 standard edition instance. I have enabled it in the properties using the Management studio and have enabled it under Sql Server Surface Area Configuration under "Database engine".
But it didn't work. I know for a fact that my connection string is correct (I tested it on the actual machine)
So I checked everything again and I noticed in the Sql Server Surface Area under "Analysis Services" there is another "remote connection" checkbox to be enabled, however it won't let me enable it there because it says the "Sql Browser" is not started. I checked and the Sql Browser is started.
I'm thinking maybe the problem there is that I have an instance of SQL 2000 running and perhaps it is the sql browser for that (I can remotely connect ot my SQL 2000 instance).
I checked under Computer management -> services and their is no SQL Browser for the instance of SQL 2005. Not even a thing to enable. Just the Default instance SQL Browser, which is for my SQL 2000 instance. Am I missing something here?
Also there is no SQL Browser to enable in the management or Surface Area Configuration programs.
I have also tried to connect using the default port number (1433) and the IP (to bypass the need for the SQL Browser). It apparently finds my Server 2000 instance because it doesn't accept my user name and password and connot find the specific database (note: I verified the username and pswd are correct by testing the values on the SQL machine). How do I find what port number a specific instance of SQL Server is running on?
Hi,the fact that you are using the default instance of SQL Server 2000 on your computer and that you can connect to the SQL Server 2k with the default settings (1433) indicates that SQL Server 2l5 is installed on another port than 1433, because ports can′t be shared. So try to see on which port you installed SQL Server 2k and on therefore on which port it is listening. Then specify the port within your connection string with the following syntax:
MachineName\instanceName,Portnumber
HTH; Jens Suessmeyer.
http://www.sqlserver2005.de
|||
I've tried figuring out what port the SQL 2005 is running on by running a port scan, but nothing really jumped out at me other than the 1433, which the SQL 2000 is running on.
What is a good way to find out what port the SQL 2005 is running on?
|||Hi,
you can have a look at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;823938
The port which SQL Server is listening to can be retrieved via looking in the eventlog or using the statement xp_readerrorlog which shows up something like "SQL Server listening on xxx.xxx.xxx.xxx:PortNumber"
HTH, Jens Suessmeyer.
http//www.sqlserver2005.de
Im getting quite desperate and frustrated!! I have installed SQL 2005 Express successfully and can connect to it fine using Management Studio locally.
If I try and connect remotely using Management Studio Im getting the old Error 10061 : The machine actively refused the connection!
I have set it up exactly as per all documentation and help from the forums. Remote connections is on TCP and Named Pipes, I even have the firewall disabled.
Im running it on Windows Server 2003 Standard.
Any help would be really apriciated as i have tried everything!!!
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||Yes I have the browser service running. Does it have any settings that need changing?|||
It would appear that the port is blocked or the server is not listening to 1433. I cant telnet to the port. How would I go about opening that up?
Cheers
|||If Browser is on and Firewall is off, your connection should work. Can you check your server's errorlog and make sure server is listening on some TCP port? One more you can check is to see if there is any outdated alias defined for yourserver\sqlexpress on your client machine? This could translate a connection string. Using Sql Server Configuration Manager and/or c:\windows\system32\cliconfg.exe to check alias.|||This is sqlexpress, most likely, it's not listening on 1433. Check your errorlog to find out which port it's listening on.|||I checked the errorlog file and found this entry:
Server is listening on [ 'any' <ipv4> 1833].
Could that be anything?
P.S. Sorry, im totally new to SQL Server!
|||The server is listening on port 1833, and it's normal. Try "telnet yourmachine 1833" and make you can connect to the port.
Sql browser should help you find the port number. If firewall is on, make sure UDP 1434 and TCP 1833 is unblocked. Did you check the alias?
|||Just in addition to Xinwei, the SQL Server browser will redirect request of clients to the appropiate port on SQL Server. In versions prior SQL 2k5 you had to know which port was held by an appropiate instance of SQL Server making your connecting string look like (as part of the server name) Servername\InstanceName,portnumber. But SQL Server Browser does something more for you, as from the
http://msdn2.microsoft.com/en-us/library/ms165724.aspx
"When SQL Server 2000 and SQL Server 2005 clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance."
Look at this link which describes also how to configure the firewall on the server.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Xinwei: I can successfully telnet to port 1833 remotely.
In the configuration manager > SQL Native Client Configuration > Aliases - there is nothing. Is this the alias you mean?
Oh and those ports are unblocked.
Should I be able to telnet 1434?
Jens: I have check that link about setting up the firewall and it is setup exactly like this yet I still have the problem!! :(
|||It's good that you can telnet to 1833. 1434 is UDP port, you cannot telnet to. Can you also try c:\windows\system32\cliconfg.exe to check the alias there, which is for MDAC. The alias you checked is for SNAC. I'm not sure which driver you are using, so I asked you to check both. By MSSMSE, do you mean MS Sql Managenet Studio or other app?
When you ping you servername, the IP was resolved correctly. right? Can you confirm SQL Browser is on and UDP port 1434 is in firewall exception? Thanks
|||
There is nothing in the ALIAS page of cliconfig! Is that correct?
Yes, Im trying to connect via Management Studio. SQL Browser is on and UDP port 1434 UDP is an exception.
I really appreciate you trying to help me as this is driving me crazy!!!!
By the way, this is a fresh installation of SQL Server Express 2005 on a fresh installation of Windows Server 2003 standard. Surely that should have gone real smooth!
|||I hope you were not confused. The alias should be checked in the client, while browser and firewall stuff should be done on the server. Can you try connect using IP address directly? and also type "xxx.xx.xx.xx, 1833" as the server name?|||THANK YOU, THANK YOU, THANK YOU.
Im in! I checked the client config util and there were 2 entries in there so i removed them! I then connected using xx.xx.xx.xx, 1833 as you suggested and i got straight in!!!!!!
That super thank you so much. But why do you think that is? Why do i have to specify the port 1833 like you said - i have never had that problem connecting to other servers?
|||Good to know it works. xx.xx.xx.xx, 1833 should work even if you do not remove the alias. Once you remove the alias, you should be able to connect the server using the servername without port number. SQL browser will help you find the port number 1833. If the port number is changed(1833 is dynamic port number), sql browser can still help you find the new port number.Im getting quite desperate and frustrated!! I have installed SQL 2005 Express successfully and can connect to it fine using Management Studio locally.
If I try and connect remotely using Management Studio Im getting the old Error 10061 : The machine actively refused the connection!
I have set it up exactly as per all documentation and help from the forums. Remote connections is on TCP and Named Pipes, I even have the firewall disabled.
Im running it on Windows Server 2003 Standard.
Any help would be really apriciated as i have tried everything!!!
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||Yes I have the browser service running. Does it have any settings that need changing?|||
It would appear that the port is blocked or the server is not listening to 1433. I cant telnet to the port. How would I go about opening that up?
Cheers
|||If Browser is on and Firewall is off, your connection should work. Can you check your server's errorlog and make sure server is listening on some TCP port? One more you can check is to see if there is any outdated alias defined for yourserver\sqlexpress on your client machine? This could translate a connection string. Using Sql Server Configuration Manager and/or c:\windows\system32\cliconfg.exe to check alias.|||This is sqlexpress, most likely, it's not listening on 1433. Check your errorlog to find out which port it's listening on.|||I checked the errorlog file and found this entry:
Server is listening on [ 'any' <ipv4> 1833].
Could that be anything?
P.S. Sorry, im totally new to SQL Server!
|||The server is listening on port 1833, and it's normal. Try "telnet yourmachine 1833" and make you can connect to the port.
Sql browser should help you find the port number. If firewall is on, make sure UDP 1434 and TCP 1833 is unblocked. Did you check the alias?
|||Just in addition to Xinwei, the SQL Server browser will redirect request of clients to the appropiate port on SQL Server. In versions prior SQL 2k5 you had to know which port was held by an appropiate instance of SQL Server making your connecting string look like (as part of the server name) Servername\InstanceName,portnumber. But SQL Server Browser does something more for you, as from the
http://msdn2.microsoft.com/en-us/library/ms165724.aspx
"When SQL Server 2000 and SQL Server 2005 clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance."
Look at this link which describes also how to configure the firewall on the server.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Xinwei: I can successfully telnet to port 1833 remotely.
In the configuration manager > SQL Native Client Configuration > Aliases - there is nothing. Is this the alias you mean?
Oh and those ports are unblocked.
Should I be able to telnet 1434?
Jens: I have check that link about setting up the firewall and it is setup exactly like this yet I still have the problem!! :(
|||It's good that you can telnet to 1833. 1434 is UDP port, you cannot telnet to. Can you also try c:\windows\system32\cliconfg.exe to check the alias there, which is for MDAC. The alias you checked is for SNAC. I'm not sure which driver you are using, so I asked you to check both. By MSSMSE, do you mean MS Sql Managenet Studio or other app?
When you ping you servername, the IP was resolved correctly. right? Can you confirm SQL Browser is on and UDP port 1434 is in firewall exception? Thanks
|||
There is nothing in the ALIAS page of cliconfig! Is that correct?
Yes, Im trying to connect via Management Studio. SQL Browser is on and UDP port 1434 UDP is an exception.
I really appreciate you trying to help me as this is driving me crazy!!!!
By the way, this is a fresh installation of SQL Server Express 2005 on a fresh installation of Windows Server 2003 standard. Surely that should have gone real smooth!
|||I hope you were not confused. The alias should be checked in the client, while browser and firewall stuff should be done on the server. Can you try connect using IP address directly? and also type "xxx.xx.xx.xx, 1833" as the server name?|||THANK YOU, THANK YOU, THANK YOU.
Im in! I checked the client config util and there were 2 entries in there so i removed them! I then connected using xx.xx.xx.xx, 1833 as you suggested and i got straight in!!!!!!
That super thank you so much. But why do you think that is? Why do i have to specify the port 1833 like you said - i have never had that problem connecting to other servers?
|||Good to know it works. xx.xx.xx.xx, 1833 should work even if you do not remove the alias. Once you remove the alias, you should be able to connect the server using the servername without port number. SQL browser will help you find the port number 1833. If the port number is changed(1833 is dynamic port number), sql browser can still help you find the new port number.I am trying to connect to a remote 2005 SQL server “A” from my 2005 SQL Server Management Studio on a different network. I have opened up port 1433 on the remote server A. I have made sure that “Allow remote connections to this server” is checked on the remote server properties under connections. I have also chosen “Using both TCP/IP and named pipes” under Remote Connections in the SQL Server 2005 Surface Area Configuration.
I am using \\xxx.xxx.xxx.xxx\sqlservername and user sa to connect. I get the following error: “An error has occurred while establishing a connection to the server. When connecting to the 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: 5 – Invalid parameter(s) found) (Microsoft SQL Server, Error:87)”
If I take out the \\ from the server path I get an error that the server not found. So I think I am getting to the server.
I have run SQL profiler and there is no event or failed login on the remote server when I try to connect to SQL server A. SQL Server A is on Windows2003 with all current patches. I have tried connecting from Windows XP pro and Windows2003 server.
Is this a network issue, Windows2003 issue, or a SQL issue?
If the server path starts with \\ the client stack assumes the connection is intended over Named Pipes, which requires the full pipe name specification, which is not the case in the above example, hence, the error "Invalid parameter(s) found".
The solution depends on whether the SQL Server is installed as a "default" or a "named" instance:
(1) For a default instance: specify the server by either (a) the server's machine name only (no \\ or \), or (b) the server machine's IP address only (again, no \\ or \).
(2) For a "named" instance:
- specify the server as either (a) machineName\instanceName, or (b) ipAddress\instanceName. Note that if you installed the Express edition it is installed as named instance with name SQLEXPRESS by default.
- grant firewall expection for the sqlservr.exe processes.
- start SQL Browser service on the server's machine.
- grant firewall expection for the sqlbrowser.exe processes.
|||This worked! Thank you!|||
I also can't connect to my SQL Server Express, running on xxx.xxx.xxx.xxx. I tryed out everything! F***
Could you write out a complete connection string, pls? I tryed:
string strConn = "Data Source=xxx.xxx.xxx.xxx; Initial Catalog=table; Connect Timeout=30; User ID=xxxxxxx; Password=xxxxxxx;";
string strConn = "Data Source=servername; Initial Catalog=table; Connect Timeout=30; User ID=xxxxxxx; Password=xxxxxxx;";
|||
I am facing a similar problem.
I have Microsoft SQL Server 2005 Express (Named Instance:=01HW050876\SQLEXPRESS) in my machine (OS : Windows XP). I am using ADO in Visual Basic 6 to connect to the database. I use SQL Server Authentication to connect to ther server. It works fine if I connect from my machine. However, if i try to connect from a different machine I get the following error:
Unhandled Error [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied
Here is my connection string:
Public Const CONNECT_STRING_SQL As String = "PROVIDER=SQLOLEDB.1;SERVER=01HW050876\SQLEXPRESS;UID=user;PWD=sqlpwd;DATABASE=SalesWindow"
I have also used the Ip address
Public Const CONNECT_STRING_SQL As String = "PROVIDER=SQLOLEDB.1;SERVER=xxx.xxx.xxx.xxx\SQLEXPRESS;UID=user;PWD=sqlpwd;DATABASE=SalesWindow"
The other computer is also in the same network and I was able to ping my machine from there. I have also set the login mode of the SQLServer to Mixed mode in the registry and have added the user from the other machine to the database and have given access rights.
Could you help me to track down the problem
|||Shasur
This blog can help you.
http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx
I am trying to connect to a remote 2005 SQL server “A” from my 2005 SQL Server Management Studio on a different network. I have opened up port 1433 on the remote server A. I have made sure that “Allow remote connections to this server” is checked on the remote server properties under connections. I have also chosen “Using both TCP/IP and named pipes” under Remote Connections in the SQL Server 2005 Surface Area Configuration.
I am using \\xxx.xxx.xxx.xxx\sqlservername and user sa to connect. I get the following error: “An error has occurred while establishing a connection to the server. When connecting to the 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: 5 – Invalid parameter(s) found) (Microsoft SQL Server, Error:87)”
If I take out the \\ from the server path I get an error that the server not found. So I think I am getting to the server.
I have run SQL profiler and there is no event or failed login on the remote server when I try to connect to SQL server A. SQL Server A is on Windows2003 with all current patches. I have tried connecting from Windows XP pro and Windows2003 server.
Is this a network issue, Windows2003 issue, or a SQL issue?
If the server path starts with \\ the client stack assumes the connection is intended over Named Pipes, which requires the full pipe name specification, which is not the case in the above example, hence, the error "Invalid parameter(s) found".
The solution depends on whether the SQL Server is installed as a "default" or a "named" instance:
(1) For a default instance: specify the server by either (a) the server's machine name only (no \\ or \), or (b) the server machine's IP address only (again, no \\ or \).
(2) For a "named" instance:
- specify the server as either (a) machineName\instanceName, or (b) ipAddress\instanceName. Note that if you installed the Express edition it is installed as named instance with name SQLEXPRESS by default.
- grant firewall expection for the sqlservr.exe processes.
- start SQL Browser service on the server's machine.
- grant firewall expection for the sqlbrowser.exe processes.
|||This worked! Thank you!|||I also can't connect to my SQL Server Express, running on xxx.xxx.xxx.xxx. I tryed out everything! F***
Could you write out a complete connection string, pls? I tryed:
string strConn = "Data Source=xxx.xxx.xxx.xxx; Initial Catalog=table; Connect Timeout=30; User ID=xxxxxxx; Password=xxxxxxx;";
string strConn = "Data Source=servername; Initial Catalog=table; Connect Timeout=30; User ID=xxxxxxx; Password=xxxxxxx;";
|||
I am facing a similar problem.
I have Microsoft SQL Server 2005 Express (Named Instance:=01HW050876\SQLEXPRESS) in my machine (OS : Windows XP). I am using ADO in Visual Basic 6 to connect to the database. I use SQL Server Authentication to connect to ther server. It works fine if I connect from my machine. However, if i try to connect from a different machine I get the following error:
Unhandled Error [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied
Here is my connection string:
Public Const CONNECT_STRING_SQL As String = "PROVIDER=SQLOLEDB.1;SERVER=01HW050876\SQLEXPRESS;UID=user;PWD=sqlpwd;DATABASE=SalesWindow"
I have also used the Ip address
Public Const CONNECT_STRING_SQL As String = "PROVIDER=SQLOLEDB.1;SERVER=xxx.xxx.xxx.xxx\SQLEXPRESS;UID=user;PWD=sqlpwd;DATABASE=SalesWindow"
The other computer is also in the same network and I was able to ping my machine from there. I have also set the login mode of the SQLServer to Mixed mode in the registry and have added the user from the other machine to the database and have given access rights.
Could you help me to track down the problem
|||Shasur
This blog can help you.
http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx
I am trying to connect to a remote 2005 SQL server “A” from my 2005 SQL Server Management Studio on a different network. I have opened up port 1433 on the remote server A. I have made sure that “Allow remote connections to this server” is checked on the remote server properties under connections. I have also chosen “Using both TCP/IP and named pipes” under Remote Connections in the SQL Server 2005 Surface Area Configuration.
I am using \\xxx.xxx.xxx.xxx\sqlservername and user sa to connect. I get the following error: “An error has occurred while establishing a connection to the server. When connecting to the 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: 5 – Invalid parameter(s) found) (Microsoft SQL Server, Error:87)”
If I take out the \\ from the server path I get an error that the server not found. So I think I am getting to the server.
I have run SQL profiler and there is no event or failed login on the remote server when I try to connect to SQL server A. SQL Server A is on Windows2003 with all current patches. I have tried connecting from Windows XP pro and Windows2003 server.
Is this a network issue, Windows2003 issue, or a SQL issue?
If the server path starts with \\ the client stack assumes the connection is intended over Named Pipes, which requires the full pipe name specification, which is not the case in the above example, hence, the error "Invalid parameter(s) found".
The solution depends on whether the SQL Server is installed as a "default" or a "named" instance:
(1) For a default instance: specify the server by either (a) the server's machine name only (no \\ or \), or (b) the server machine's IP address only (again, no \\ or \).
(2) For a "named" instance:
- specify the server as either (a) machineName\instanceName, or (b) ipAddress\instanceName. Note that if you installed the Express edition it is installed as named instance with name SQLEXPRESS by default.
- grant firewall expection for the sqlservr.exe processes.
- start SQL Browser service on the server's machine.
- grant firewall expection for the sqlbrowser.exe processes.
|||This worked! Thank you!|||I also can't connect to my SQL Server Express, running on xxx.xxx.xxx.xxx. I tryed out everything! F***
Could you write out a complete connection string, pls? I tryed:
string strConn = "Data Source=xxx.xxx.xxx.xxx; Initial Catalog=table; Connect Timeout=30; User ID=xxxxxxx; Password=xxxxxxx;";
string strConn = "Data Source=servername; Initial Catalog=table; Connect Timeout=30; User ID=xxxxxxx; Password=xxxxxxx;";
|||
I am facing a similar problem.
I have Microsoft SQL Server 2005 Express (Named Instance:=01HW050876\SQLEXPRESS) in my machine (OS : Windows XP). I am using ADO in Visual Basic 6 to connect to the database. I use SQL Server Authentication to connect to ther server. It works fine if I connect from my machine. However, if i try to connect from a different machine I get the following error:
Unhandled Error [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied
Here is my connection string:
Public Const CONNECT_STRING_SQL As String = "PROVIDER=SQLOLEDB.1;SERVER=01HW050876\SQLEXPRESS;UID=user;PWD=sqlpwd;DATABASE=SalesWindow"
I have also used the Ip address
Public Const CONNECT_STRING_SQL As String = "PROVIDER=SQLOLEDB.1;SERVER=xxx.xxx.xxx.xxx\SQLEXPRESS;UID=user;PWD=sqlpwd;DATABASE=SalesWindow"
The other computer is also in the same network and I was able to ping my machine from there. I have also set the login mode of the SQLServer to Mixed mode in the registry and have added the user from the other machine to the database and have given access rights.
Could you help me to track down the problem
|||Shasur
This blog can help you.
http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx
I am trying to connect to a remote 2005 SQL server “A” from my 2005 SQL Server Management Studio on a different network. I have opened up port 1433 on the remote server A. I have made sure that “Allow remote connections to this server” is checked on the remote server properties under connections. I have also chosen “Using both TCP/IP and named pipes” under Remote Connections in the SQL Server 2005 Surface Area Configuration.
I am using \\xxx.xxx.xxx.xxx\sqlservername and user sa to connect. I get the following error: “An error has occurred while establishing a connection to the server. When connecting to the 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: 5 – Invalid parameter(s) found) (Microsoft SQL Server, Error:87)”
If I take out the \\ from the server path I get an error that the server not found. So I think I am getting to the server.
I have run SQL profiler and there is no event or failed login on the remote server when I try to connect to SQL server A. SQL Server A is on Windows2003 with all current patches. I have tried connecting from Windows XP pro and Windows2003 server.
Is this a network issue, Windows2003 issue, or a SQL issue?
If the server path starts with \\ the client stack assumes the connection is intended over Named Pipes, which requires the full pipe name specification, which is not the case in the above example, hence, the error "Invalid parameter(s) found".
The solution depends on whether the SQL Server is installed as a "default" or a "named" instance:
(1) For a default instance: specify the server by either (a) the server's machine name only (no \\ or \), or (b) the server machine's IP address only (again, no \\ or \).
(2) For a "named" instance:
- specify the server as either (a) machineName\instanceName, or (b) ipAddress\instanceName. Note that if you installed the Express edition it is installed as named instance with name SQLEXPRESS by default.
- grant firewall expection for the sqlservr.exe processes.
- start SQL Browser service on the server's machine.
- grant firewall expection for the sqlbrowser.exe processes.
|||This worked! Thank you!|||I also can't connect to my SQL Server Express, running on xxx.xxx.xxx.xxx. I tryed out everything! F***
Could you write out a complete connection string, pls? I tryed:
string strConn = "Data Source=xxx.xxx.xxx.xxx; Initial Catalog=table; Connect Timeout=30; User ID=xxxxxxx; Password=xxxxxxx;";
string strConn = "Data Source=servername; Initial Catalog=table; Connect Timeout=30; User ID=xxxxxxx; Password=xxxxxxx;";
|||
I am facing a similar problem.
I have Microsoft SQL Server 2005 Express (Named Instance:=01HW050876\SQLEXPRESS) in my machine (OS : Windows XP). I am using ADO in Visual Basic 6 to connect to the database. I use SQL Server Authentication to connect to ther server. It works fine if I connect from my machine. However, if i try to connect from a different machine I get the following error:
Unhandled Error [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied
Here is my connection string:
Public Const CONNECT_STRING_SQL As String = "PROVIDER=SQLOLEDB.1;SERVER=01HW050876\SQLEXPRESS;UID=user;PWD=sqlpwd;DATABASE=SalesWindow"
I have also used the Ip address
Public Const CONNECT_STRING_SQL As String = "PROVIDER=SQLOLEDB.1;SERVER=xxx.xxx.xxx.xxx\SQLEXPRESS;UID=user;PWD=sqlpwd;DATABASE=SalesWindow"
The other computer is also in the same network and I was able to ping my machine from there. I have also set the login mode of the SQLServer to Mixed mode in the registry and have added the user from the other machine to the database and have given access rights.
Could you help me to track down the problem
|||Shasur
This blog can help you.
http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx
