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 webwork place

We have a SBS2003 server. And configured remote workplace to allow remote users

access to their workstion. On the windows XP Pro sp2 station I have added the user to the "Remote desktop" list ( control panel-system-remote-allow users to remotely connect this computer - Add .)

Nomal users can pass router and select wanted station then get logon screen. But this error message is showed up:

"The local policy of this system does not permit you to logon interractively."

The administrator can logon to any station remotely. Do I have to give additional permission to normal user?

Thank you for any help or clue.

Peter

THis seems more a Windows problem than a SQL Server (Express) problem. You might try reaching someone on the public newsgroups covering SBS 2003.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Peter,

Control Panel -> Administrative Tools -> Local Security Policy -> Local Policy -> User Rights Assignments -> Log On Locally

@. here you have to add the Domain Users you wish to grant log on to your system (Remote Server)

Regards

Hemantgiri S. Goswami

|||

Hemantgiri,

Thank you very much. It solved my problem.

Peter