Remote(over a network) Database Connection Issue

This has to be an easy question.

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

WORK3\COMPANY
sa,password

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

Thanks in Advance

Are

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

Are you using a connection string? which is?

|||

My VB6 connectionstring is:

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

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

mstrPassword ="password"

mstrDataSource =<my database name>

goSQLDataSource= "WORK/SQLSERVER"

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

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

|||

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

|||

are you refering to this?

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

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

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

Does that answer your question?

|||

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

|||

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

|||

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

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

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

|||

Maximum number of concurrent connections=0

Use query gov. = unchecked

default connection options= all unchecked

allow remote connections to this server=checked

remote query timeout=0

require distrubited transactions=unchecked

Configured values=selected

sql

Remote connection to SSIS fails with Access is Denied

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

Remote connection failed, even with proper configuration

I am unable to connect to a SQL instance on the server from a workstation on the same network.

SQL Express SP1 is installed on the server, and allows mixed authentication. The instance name is <SERVERNAME>\SQLEXPRESS.

Both the Database and Browser services are running.

Local and remote connections are enabled under SAC. I also used sp_configure to make the same changes.

All Protocols for SQLEXPRESS have been Enabled using the Configuration Manager.

The firewalls on both machines have been disabled.

Rebooting the server or restarting services after various changes hasn't affected the outcome.

Local connections made directly on the server are always successful using either authentication mode.

SQL Native Client Driver is installed on the workstation.

The workstation user is trusted. However, neither authentication mode is successful.

The connection string from the workstation uses the named instance above and the SQL Native Client driver.

Other services on the server are working and avaialble to the workstation, such as shared folders.

When I attempt a connection from the workstation I receive the following errors:

Connection failed:

SQLState: '08001'

SQL Server Error: -1

[Microsoft][SQL Native Client]SQL Networtk Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Connection failed:

SQLState: 'HYT00'

SQL Server Error: 0

[Microsoft][SQL Native Client]Login timeout expired

Connection failed:

SQLState: '08001'

SQL Server Error: -1

[Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default setting SQL Server does not allow remote connections.

I have tried all of the recommendations I can find here without any luck. What am I missing?

Hi,

can you post full connection string used to connection machines!!?

Are you able to get connected each other servers from SSMS?

Hemantgiri S. Goswami

|||

Try to connect to the Server directly using the appropiate port rather than just using the instance name (to see if the SQL Browser is causing the problem) Use the format Servername,portnumber for this. If this does not work, you might have not enabled TCP/IP for connections.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||THank you for the suggestions. The problem was caused by an unrelated program on the same server. Uninstalling the offending program, and reinstalling SQL Express, did the trick.|||

Would you mind posting what the offending program was? I'm getting the same reaction from SQL Server 2005 Express in my LAN with an application that was running just a couple of weeks ago.

TIA

Mike Baker
Adobe Community Expert