permissions sql express and remote connection problems TIA

posted to the forums a few weeks ago, followed the suggestion but the problem has not been squared, the link for my original post is at the bottom of this message.

have tried most of the suggestions in the forums, again see the link for details on steps i have tried, kept poking around and came up with a few more possibilities for the problem,

the error message is the one that says sql server express remote connnections are not enabled by default during the install of sql express,

backed up a little and it seems i can not even connect to the local machine, i ran the sqlcmd -s -e \sqlexpress and the login timesouts and says that sql express refused the connection, have shared memory protocal and tcp/ip running also tried it with named pipes,

so that left me wonderin if maybe permission for sql express need to be adjusted, brought up SQL server Studio Mangement Express console and went to the only express instance on the local machine, brought up properties for \sqlexpress and went to the permission section and noticed the following:

for the BuiltIn\administrator and builtIn\User account the only permission that is checked off is the "Conect SQL" which has only the Grant checkbox checked, also the grantor is sa, btw what does the checkbox With Grant do? this was the same for the both accounts,

for the public account the only permission that is enabled is the View any database,

are there other permission which need to be granted or "with granted", also i assume i should not be using the sa account or should i?

other things going on include include Visual Studio 2005, i am able to get to the database thru Server Explorer, and can bring up the tables and see them, sometimes thos their is a red x on the mdf, but when i click it brings them up.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=217258&SiteID=1

Are you using a named instance? What instance names do you have in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL?

"sqlcmd -E -S \sqlexpress" will work only if the main instance is called sqlexpress, if it is called differently, then you have to use that name.

Have you ever been able to connect locally with sqlcmd? Let's figure out first what's wrong with the local connection, and then we can investigate the remote issues.

Thanks
Laurentiu

|||

Thanks Lauretiu, check the registry and it looks alright, the keys are below:

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.

the aspnet_regsql error message is very similar,

|||

Here's a link that discusses some connection failures and mentions the 0x274D error:

http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx

According to Peter, one of the reasons for this message could be that TCP is not enabled. Could you check that the TCP protocol is enabled? You can use the Configuration Manager and look under SQL Server 2005 Network Configuration->Protocols for SQLEXPRESS. I'll see if I can find anything else about this error.

Thanks
Laurentiu

|||

This may be just a typo in the post but the correct syntax for the sqlcmd command run on the same machine where SQL Express runs is:

sqlcmd -E -S .\sqlexpress

Note:

- the capital -E and -S (the case matters in the option selection)

- the dot before the \ character

In your original post you mentioned that the SQL Express lists port 1232 under IPAll. If you get the same error ("TCP Provider: No connection could be made because the target machine actively refused it") after running the above sqlcmd statement, you can troubleshoot it by running:

telnet <machineName> 1232

- replace <machineName> with the actual name of your machine.

- if it fails with an error similar to "Could not open connection to the host" it means SQL Express is not configured for TCP, and may need to be restarted.

- if it gives you a blank screen it means it connected, and you should be able to connect through

sqlcmd -E -S tcp:.\sqlexpress,1232

|||

Hey Peter thank

it was the capital -S that was causing the problem, a weeks delay over a cap, should know better by now, thanks again, take care