Connection handshake failed - easiest possible configuration

Hi there!

Often discussed, but not really solved in my opinion - the connection between the partners and the witness causes problems.

My case: Three Servers in the same domain, three endpoints on 5022 with windows negotiation, all endpoints can be reached by telnet from each server. Mirrorring works. So far so good.

But one of these partners is not able to connect to the witness. The witness' error log is full with that:

"2006-06-01 13:45:20.32 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(Der Anmeldeversuch ist fehlgeschlagen.). State 67.'. [CLIENT: 130.143.205.54]"

My Endpoints are created like

CREATE ENDPOINT [EASYRIS_Mirroring]

AUTHORIZATION [code1\dephbrsaa1-sys108]

STATE=STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE

, ENCRYPTION = SUPPORTED ALGORITHM RC4);

What catches my eyes is that

GRANT CONNECT ON ENDPOINT::EASYRIS_Mirroring TO [code1\dephbrsaa1-sys108];

doesn't cause these user to appear in the result set of

SELECT EP.name, SP.STATE,

CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))

AS GRANTOR,

SP.TYPE AS PERMISSION,

CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))

AS GRANTEE

FROM sys.server_permissions SP , sys.endpoints EP

WHERE SP.major_id = EP.endpoint_id

ORDER BY Permission,grantor, grantee;

By the way, these mentioned user is sysadmin and grantor.

Has anyone an idea?

Torsten

So,

The 8009030c error from the OS indicates that there is a login error at the OS level. SQL isn't involved with the networking protocol yet. So, look at the credentials that SQL Server is running under. There may need to be a restart of the SQL Server process to pick up the new credentials.

Thanks,

Mark

|||

Hi Mark,

thanks, that was the missing information. I could resolve the issue:

It seems that 1. the SQL Server Processes of each partner and the wittness has to run under an equal domain user and 2. these domain user must be local admin.

Can someone confirm these thesis?

Thanks a lot, Torsten

|||

You do not have to run all the same accounts and run as the SA to setup mirroring. It is just that the easiest way to setup mirroring is to have all the accounts be the same and SA.

You can use different accounts on the servers, but they need to be granted access to the other endpoints.

You can also run as local system accounts, but you need to setup certificattes. It is all in BOL.

Thanks,

Mark

Connecting via Radio Link

Could somebody tell me as to what configuration changes has to be made in the SQL Server 2000, if I want to connect to a table in the database using Internet options, i.e., I have the SQL Server running on a machine which uses a real IP address, and I want to connect to it (SQL Server)using DSN from another location. In the 2nd location, I use internet using dialup/DSL/Cable Broadband.

--
The following error is shown, when I try to connect to the SQL Server using SQL Server authentication using login ID and password:

Connection failed:
SQL State:'01000'
SQL Server Error: 10060
[Microsoft][OBDC SQL Server Driver][TCP/IP Sockets]ConnectionOpen[Connect()]
Connection failed:
SQL State:'08001'
SQL Server Error: 17
[Microsoft][OBDC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
-----

Thanxs in advance to anyone who can help me solve the problem.You can use the Client Network Utility.

Go To Client Network Utility. Go to Alias Tab.
Click on ADD. Provide a Alias Name, and the Server IP address or the URL as Server Name under the Connection Parameters Section. (Netowrk Libraries can be Named Pipes)

Thats it. When you go to Enterprise Manager, you can register that server by its Alias name.

Connecting to the server

Hi everyone, my friend is creating an online game and he needs help with something, he made the formatting for the configuration like this:
"AccountDbIP"="example.com"
"AccountDbID"="ABC"
"AccountDbPwd"="123"
"AccountDbName"="AccountDB"

But his dedicated server host is running windows 2003 web edition and it only allows mssql express. And he cannot connect just by using the ip, he needs to use ip/SQLEXPRESS, can anyone tell us how to connect to the server database using the IP only?

Thanks and best regards to all,
Bob R

for remote connections the format is "[ServerName or IP][\Named Instance][,Port#]"

for local connections (game is running on the same box as the SQL Server) the format is "[.][\Named Instance]"

Each of these methods uses whats called a different protocol and there is actually a few more including Named Pipes but I am assuming you do not wish to use that protocol.

D

|||

As far as I know, you must provide the Instance Name when you are connecting to a Named Instance. This is a requirement of SQL Server, not just SQL Express. The only way you could connect using only the IP would be if your friends Hosting company installed SQL Express as the Default Instance rather than as a Named Instance.

I'm not sure I understand why your friend can not use the Named Instance. As you've indicated, just use IP\SQLEXPRESS in your connection string. What is the problem with this?

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

|||

Bob, please update thread/mark an answer.

thanks,

Derek

Connecting to SQL Server 2005 on local machine via an alias in the hosts file

Don't know if it works for .NET but creating an Alias could be the solution
to your problem. See SQL Server Configuration Manager | SQL Native Client
Configuration | Alias. Using an alias is usually much better than fiddling
with the Host file but as I said, I never tried them with .NET.
Finally, from your connection string, I'm not sure if you are using the
Native Provider for SQL-Server 2005 instead of the older provider for
SQL-Server 2000. Maybe your problem with 127.0.0.1 comes from that. I also
don't understand why you are using the connection reset=false parameter.
This parameter should only be used when you are not using the connection
pooling.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Sam" <samchurchill82@.gmail.com> wrote in message
news:45ea7082-6c27-4f12-85a8-0c33aec870d7@.b15g2000hsa.googlegroups.com...
> Hi,
> I work in a team of .NET developers who all have a local version of
> SQL Server 2005 on their PC for normal development projects. However,
> sometimes more than one of us has to work on the same server for
> development as we are sharing data. If we change the server name in
> the connection string in our application's config file from "(local)"
> to "SharedDevelopmentServer" and check it in to source control all of
> the other developers will get it and suddenly be using the shared
> server rather that their own local server. We got into the situation
> where the config file was being checked in and out every 15 minutes
> because one group wanted it to be "(local)" and the other group wanted
> it to be "SharedDevelopmentServer".
> I thought I had an elegant solution to this: make the server in the
> config file's connection string point to a non-existant server called
> "DevelopmentServer", then each developer could add a record into his
> HOSTS file to point this DNS alias to the IP address of the server
> that they want to use at that time. This works very well if the
> development server that you want to point to is not localhost but for
> some reason it gives the following error if you try to use your own IP
> address (or the loopback 127.0.0.1 address).
> Login failed for user ''. The user is not associated with a trusted
> SQL Server connection.
> It looks as if it's not passing through my credentials properly so
> it's not able to authenticate me. Our connection string is as
> follows:
> <add name="MyConnectionString" connectionString="data
> source=DevelopmentServer;database=MyDatabase;Integ rated
> Security=SSPI;application name=MyApplication;Connection Reset=false"
> providerName="System.Data.SqlClient" />
> Has anyone got any ideas how to resolve this or use another way of
> mapping to our selected development server which could be different
> for everyone in the team?
> Many thanks,
> Sam
"Sam" <samchurchill82@.gmail.com> wrote in message
news:595337f6-a7ae-4161-aad2-10d871991aac@.q77g2000hsh.googlegroups.com...
> On 11 Dec, 17:18, "Sylvain Lafontaine" <sylvain aei ca (fill the
> blanks, no spam please)> wrote:
> Thanks, that works a treat. I guess any application trying to connect
> to an SQL server on my machine must be going via this before it hits
> DNS.
To my knowledge, aliases are only for the client side; the server never sees
them; so the answer would be yes: any application willing to use an alias
must set it up on its side. Notice that you can also use a TCP/IP address
(like 127.0.0.1) as an alias; so they are definitely looked up before the
DNS servers.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)