SQL Configure: following

Showing posts with label following. Show all posts

Sunday, 25 March 2012

connect to specific session using AMO?

The following works, but it uses AdomdClient to connect to a specific session. I'm wondering if there's a way to use AMO to connect to a specific session that already exists.

Dim server As New Microsoft.AnalysisServices.Server()

server.Connect("Data Source=myserver")

Dim sOldSession As String = server.SessionID

'do something

server.Disconnect(False) 'disconnect without killing the session

Dim conn As New Microsoft.AnalysisServices.AdomdClient.AdomdConnection("Data Source=myserver")

conn.SessionID = sOldSession

conn.Open() 'works

hello,

there should be a second overload of Server.Connect that accepts sessionId as a second argument.

so something like below should work:

Dim server As New Microsoft.AnalysisServices.Server()

server.Connect("Data Source=localhost")

Dim sOldSession As String = server.SessionID

'do something

server.Disconnect(False) 'disconnect without killing the session

Dim server2 As New Microsoft.AnalysisServices.Server()

server.Connect("Data Source=localhost", sOldSession)

'do something

server2.Disconnect()

hope this helps,

Connect to remote RS in SSMS

Got following error when try to connect to remote reporting service in SSMS:

TITLE: Microsoft SQL Server Management Studio

The request failed with HTTP status 401: Unauthorized. (Microsoft.SqlServer.Management.UI.RSClient)

BUTTONS:

No problem to connect to reporting service in SSMS on RS box locally. Use windows authentication with same account in both case. How to configure that? Thanks.

Odd - You're logged in on as the same user on both computers? Are both computers in the same domain?

Can you access the report server in a web browser from the remote computer?

If you can, try this:

in the SSMS connection dialog. Replace machine\instance in the server textbox with the URL to your report server, e.g. http://yourserver/reportserver.

Does that work?

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||Yes, I can access the report server in a web browser from same remote computer where I tried to connect RS in SSMS. Replacing server name with RS url doesn't work either, got same 401 error.|||Is the user in the Administrators group on the box?|||Yes, it's local admin on both server and client machine.|||Very strange.. Since it is a 401 error, maybe you could look in the %windir%\system32\LogFiles\W3SVC1 for the latest log file to see what request is being denied.|||

Another thing to try is to change the Authentication Type to Digest in Management Studio when logging in from the remote machine. Type in your windows credentials and see if you get the same error.

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I found followings in the log file, any hint? By the way, got same error when try to connect with basic authentication. Thanks.

2005-12-30 14:15:33 172.20.193.76 GET /reportsdr - 80 - 10.16.55.81 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.2;+SV1;+.NET+CLR+1.1.4322) 401 2 2148074254

2005-12-30 14:15:33 172.20.193.76 GET /reportsdr - 80 - 10.16.55.81 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.2;+SV1;+.NET+CLR+1.1.4322) 401 1 0

2005-12-30 14:15:33 172.20.193.76 GET /reportsdr - 80 corp\rmiao 10.16.55.81 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.2;+SV1;+.NET+CLR+1.1.4322) 301 0 0

2005-12-30 14:15:55 172.20.193.76 GET /reportsdr/home.aspx - 80 corp\rmiao 10.16.55.81 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.2;+SV1;+.NET+CLR+1.1.4322) 302 0 0

|||

Looks like you're passing the Report Manager virutal directory. You need to pass the report server virtual directory.

By default these are:

Report Manager: http://localhost/reports

Report Server: http://localhost/reportserver

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||Worked with report server url, thanks for the help.|||Glad to see it worked for you.

Can you provide the IIS logs for when you try to connect using just

server\instance in the SSMS connection dialog. I'm curious to

know why you can't connect to RS with management studio.

Thanks,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.|||

Did you ever find out why you couldn't just us the servername in SSMS? I am having this same problem.

Thanks,

Bryan

|||No, fine with me to use url.

Connect to remote RS in SSMS

Got following error when try to connect to remote reporting service in SSMS:

TITLE: Microsoft SQL Server Management Studio

The request failed with HTTP status 401: Unauthorized. (Microsoft.SqlServer.Management.UI.RSClient)

BUTTONS:

No problem to connect to reporting service in SSMS on RS box locally. Use windows authentication with same account in both case. How to configure that? Thanks.

Odd - You're logged in on as the same user on both computers? Are both computers in the same domain?

Can you access the report server in a web browser from the remote computer?

If you can, try this:

in the SSMS connection dialog. Replace machine\instance in the server textbox with the URL to your report server, e.g. http://yourserver/reportserver.

Does that work?

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

Another thing to try is to change the Authentication Type to Digest in Management Studio when logging in from the remote machine. Type in your windows credentials and see if you get the same error.

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I found followings in the log file, any hint? By the way, got same error when try to connect with basic authentication. Thanks.

2005-12-30 14:15:33 172.20.193.76 GET /reportsdr - 80 - 10.16.55.81 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.2;+SV1;+.NET+CLR+1.1.4322) 401 2 2148074254

2005-12-30 14:15:33 172.20.193.76 GET /reportsdr - 80 - 10.16.55.81 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.2;+SV1;+.NET+CLR+1.1.4322) 401 1 0

2005-12-30 14:15:33 172.20.193.76 GET /reportsdr - 80 corp\rmiao 10.16.55.81 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.2;+SV1;+.NET+CLR+1.1.4322) 301 0 0

2005-12-30 14:15:55 172.20.193.76 GET /reportsdr/home.aspx - 80 corp\rmiao 10.16.55.81 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.2;+SV1;+.NET+CLR+1.1.4322) 302 0 0

|||

Looks like you're passing the Report Manager virutal directory. You need to pass the report server virtual directory.

By default these are:

Report Manager: http://localhost/reports

Report Server: http://localhost/reportserver

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||Worked with report server url, thanks for the help.|||Glad to see it worked for you.
Can you provide the IIS logs for when you try to connect using just server\instance in the SSMS connection dialog. I'm curious to know why you can't connect to RS with management studio.
Thanks,
-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.|||

Did you ever find out why you couldn't just us the servername in SSMS? I am having this same problem.

Thanks,

Bryan

|||No, fine with me to use url.

Connect to remote RS in SSMS

Got following error when try to connect to remote reporting service in SSMS:

TITLE: Microsoft SQL Server Management Studio

The request failed with HTTP status 401: Unauthorized. (Microsoft.SqlServer.Management.UI.RSClient)

BUTTONS:

No problem to connect to reporting service in SSMS on RS box locally. Use windows authentication with same account in both case. How to configure that? Thanks.

Odd - You're logged in on as the same user on both computers? Are both computers in the same domain?

Can you access the report server in a web browser from the remote computer?

If you can, try this:

in the SSMS connection dialog. Replace machine\instance in the server textbox with the URL to your report server, e.g. http://yourserver/reportserver.

Does that work?

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

Another thing to try is to change the Authentication Type to Digest in Management Studio when logging in from the remote machine. Type in your windows credentials and see if you get the same error.

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I found followings in the log file, any hint? By the way, got same error when try to connect with basic authentication. Thanks.

2005-12-30 14:15:33 172.20.193.76 GET /reportsdr - 80 - 10.16.55.81 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.2;+SV1;+.NET+CLR+1.1.4322) 401 2 2148074254

2005-12-30 14:15:33 172.20.193.76 GET /reportsdr - 80 - 10.16.55.81 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.2;+SV1;+.NET+CLR+1.1.4322) 401 1 0

2005-12-30 14:15:33 172.20.193.76 GET /reportsdr - 80 corp\rmiao 10.16.55.81 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.2;+SV1;+.NET+CLR+1.1.4322) 301 0 0

2005-12-30 14:15:55 172.20.193.76 GET /reportsdr/home.aspx - 80 corp\rmiao 10.16.55.81 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.2;+SV1;+.NET+CLR+1.1.4322) 302 0 0

|||

Looks like you're passing the Report Manager virutal directory. You need to pass the report server virtual directory.

By default these are:

Report Manager: http://localhost/reports

Report Server: http://localhost/reportserver

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

Did you ever find out why you couldn't just us the servername in SSMS? I am having this same problem.

Thanks,

Bryan

|||No, fine with me to use url.

Connect to remote RS in SSMS

Got following error when try to connect to remote reporting service in SSMS:

TITLE: Microsoft SQL Server Management Studio

The request failed with HTTP status 401: Unauthorized. (Microsoft.SqlServer.Management.UI.RSClient)

BUTTONS:

No problem to connect to reporting service in SSMS on RS box locally. Use windows authentication with same account in both case. How to configure that? Thanks.

Odd - You're logged in on as the same user on both computers? Are both computers in the same domain?

Can you access the report server in a web browser from the remote computer?

If you can, try this:

in the SSMS connection dialog. Replace machine\instance in the server textbox with the URL to your report server, e.g. http://yourserver/reportserver.

Does that work?

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

Another thing to try is to change the Authentication Type to Digest in Management Studio when logging in from the remote machine. Type in your windows credentials and see if you get the same error.

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I found followings in the log file, any hint? By the way, got same error when try to connect with basic authentication. Thanks.

2005-12-30 14:15:33 172.20.193.76 GET /reportsdr - 80 - 10.16.55.81 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.2;+SV1;+.NET+CLR+1.1.4322) 401 2 2148074254

2005-12-30 14:15:33 172.20.193.76 GET /reportsdr - 80 - 10.16.55.81 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.2;+SV1;+.NET+CLR+1.1.4322) 401 1 0

2005-12-30 14:15:33 172.20.193.76 GET /reportsdr - 80 corp\rmiao 10.16.55.81 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.2;+SV1;+.NET+CLR+1.1.4322) 301 0 0

2005-12-30 14:15:55 172.20.193.76 GET /reportsdr/home.aspx - 80 corp\rmiao 10.16.55.81 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.2;+SV1;+.NET+CLR+1.1.4322) 302 0 0

|||

Looks like you're passing the Report Manager virutal directory. You need to pass the report server virtual directory.

By default these are:

Report Manager: http://localhost/reports

Report Server: http://localhost/reportserver

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

Did you ever find out why you couldn't just us the servername in SSMS? I am having this same problem.

Thanks,

Bryan

|||No, fine with me to use url.sql sql

Connect To Oracle 10g 64 Bit using SQL Server Integration Services 2005 64 Bit

I am trying to create a oracle 10g connection in SSIS using connection manager and i am getting the following error,

Test Connection failed because of an error in initializing provider. Oracle client and networking

components were not found. These components are supplied by Oracle corporation and are part of the

Oracle version 7.3.3 or later client software installation.

Provider is unable to function until these components are installed.

Can someone please help.

Thanks.

If your operating System and VS2005 are 32bits then you need to download and install the Oracle 10g client for Windows 32bits, when I last downloaded it was more than 1gig install it and create your connection to the database make sure your TNS ORA works before adding SSIS connection to Oracle. The client tools are free but you need an Oracle Technet account which you can create for free. The Developer is also free it let you see Oracle tables and databases. If you are in an employer's network I find connecting with Named Pipes is easier than TCP/IP. One more thing watch the installer. Hope this helps.

http://www.oracle.com/technology/software/index.html

I forgot to add you must compile your code with AnyCPU.

|||

Thanks for your reply Caddre..

But.. i have MS 2003 64 bit OS.. i have installed oracle 10g 64 bit server.. i can successfully connect thru sqlplus.. no problem...

I am missing something on SQL 2005 and i am not sure what it is...

|||

So you are saying you have installed Oracle 10g 64bits client tools in the box? I know connecting through SQL plus does not solve development connection issue you need the client tools in your box this time 64bits. Another thing you are not missing anything but I think VS2005 unless you buy 64bits full version is 32bits that is not all your setup is 64bits, so you need to either make it all 64bits or develop in 32bits and deploy in 64bits because I read there are issues known issues.

|||

yes - as far as i know all are 64bit

I cannot connect thru both providers - OLEDB and Net Oracle client

|||

Try this recent Microsoft support article steps, I will still look for more info.

http://support.microsoft.com/kb/255084

|||

Hi Caddre,

Either you can have a look at summarized article here. or You may look for "Getting SSIS Packages To Run On A 64 Bit Box" in Sutha Thiru's Blog .

Thanks

Subhash Subramanyam

|||

thanks subash.. awesome..

those articles helped me to fix the issue...

appreciate yours and caddre help on this..

|||I am glad to see your problem is resolved and Subhash thanks for the articles much appreciated.

Tuesday, 20 March 2012

connect to a specific port on sql server/endpoint

i have created a new tcp endpoint using the following:
CREATE ENDPOINT [CustomConnection1]
STATE = STARTED
AS TCP
(LISTENER_PORT = 1444, LISTENER_IP =ALL)
FOR TSQL() ;
And I got warning about permissions being revoked for the default tcp
endpoint, but I was okay with that since I really wanted all users to go
through this new endpoint I am creating.
I then granted connect pemissions to a sql login (called test) to connect to
this endpoint.
I added the tcp port 1444 to the IPALL TCP Port list & restarted SQL.
I tried connecting to the sql server with the test login & it gave me a
login failed error 18456.
i then granted connect permissions to public to the default tcp port & tried
conecting with the test user & it connected successfully. So obviously it was
connecting with the default tcp port & not the new port i defined as the
endpoint. to be sure, i stopped the new endpoint & connected with the test
login again & it still worked.
So my question is - what am i doing wrong? i dont connections to go through
the default tcp port , i want them to go through the port i define in the
endpoint.
also, how can i connect to a specific port through management studio?
just fyi - i basically followed the instructions on this link:
http://msdn2.microsoft.com/en-us/library/ms189310.aspx
thanks in advance!Did you specify this new port in the connection string/server name?
It seems you just want to change the port, and for that you don't need to create a new endpoint. You
would just change the port in Server Configuration program.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"adba" <adba@.discussions.microsoft.com> wrote in message
news:04055949-5892-425E-8593-541612857169@.microsoft.com...
>i have created a new tcp endpoint using the following:
> CREATE ENDPOINT [CustomConnection1]
> STATE = STARTED
> AS TCP
> (LISTENER_PORT = 1444, LISTENER_IP =ALL)
> FOR TSQL() ;
> And I got warning about permissions being revoked for the default tcp
> endpoint, but I was okay with that since I really wanted all users to go
> through this new endpoint I am creating.
> I then granted connect pemissions to a sql login (called test) to connect to
> this endpoint.
> I added the tcp port 1444 to the IPALL TCP Port list & restarted SQL.
> I tried connecting to the sql server with the test login & it gave me a
> login failed error 18456.
> i then granted connect permissions to public to the default tcp port & tried
> conecting with the test user & it connected successfully. So obviously it was
> connecting with the default tcp port & not the new port i defined as the
> endpoint. to be sure, i stopped the new endpoint & connected with the test
> login again & it still worked.
> So my question is - what am i doing wrong? i dont connections to go through
> the default tcp port , i want them to go through the port i define in the
> endpoint.
> also, how can i connect to a specific port through management studio?
> just fyi - i basically followed the instructions on this link:
> http://msdn2.microsoft.com/en-us/library/ms189310.aspx
> thanks in advance!
>|||No, I havenâ't specified this new port in my servername â' how do I do that in
management studio? Lets assume my server name is sqltest & the new port
number is 1443 & the endpoint is called custom1. should the servername be
sqltest:1443 ?
What I want to do is create different tcp endpoints for different
applications. That way the default port 1433 would be used only by admins
internally & other users outside the firewall will connect through another
port (new endpoint) than 1433. That way if security is compromised with one
application, I donâ't have to shut the entire server down, I should be able to
shut down that specific port â' does this idea make sense' Iâ'm not having
much luck implementing itâ?¦.
"Tibor Karaszi" wrote:
> Did you specify this new port in the connection string/server name?
> It seems you just want to change the port, and for that you don't need to create a new endpoint. You
> would just change the port in Server Configuration program.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "adba" <adba@.discussions.microsoft.com> wrote in message
> news:04055949-5892-425E-8593-541612857169@.microsoft.com...
> >i have created a new tcp endpoint using the following:
> > CREATE ENDPOINT [CustomConnection1]
> > STATE = STARTED
> > AS TCP
> > (LISTENER_PORT = 1444, LISTENER_IP =ALL)
> > FOR TSQL() ;
> > And I got warning about permissions being revoked for the default tcp
> > endpoint, but I was okay with that since I really wanted all users to go
> > through this new endpoint I am creating.
> > I then granted connect pemissions to a sql login (called test) to connect to
> > this endpoint.
> > I added the tcp port 1444 to the IPALL TCP Port list & restarted SQL.
> > I tried connecting to the sql server with the test login & it gave me a
> > login failed error 18456.
> > i then granted connect permissions to public to the default tcp port & tried
> > conecting with the test user & it connected successfully. So obviously it was
> > connecting with the default tcp port & not the new port i defined as the
> > endpoint. to be sure, i stopped the new endpoint & connected with the test
> > login again & it still worked.
> > So my question is - what am i doing wrong? i dont connections to go through
> > the default tcp port , i want them to go through the port i define in the
> > endpoint.
> > also, how can i connect to a specific port through management studio?
> >
> > just fyi - i basically followed the instructions on this link:
> > http://msdn2.microsoft.com/en-us/library/ms189310.aspx
> >
> > thanks in advance!
> >
> >
>|||I understand about your usage scenario. Yes, that makes sense to me.
To connect to so something else than the "default" port, the client uses below syntax for the server
name. Note that you don't specify an instance name if it is a named instance:
SERVERNAME,PORT#
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"adba" <adba@.discussions.microsoft.com> wrote in message
news:C84D70CE-AEC4-48F3-B56E-86C264C85858@.microsoft.com...
> No, I havenâ't specified this new port in my servername â' how do I do that in
> management studio? Lets assume my server name is sqltest & the new port
> number is 1443 & the endpoint is called custom1. should the servername be
> sqltest:1443 ?
> What I want to do is create different tcp endpoints for different
> applications. That way the default port 1433 would be used only by admins
> internally & other users outside the firewall will connect through another
> port (new endpoint) than 1433. That way if security is compromised with one
> application, I donâ't have to shut the entire server down, I should be able to
> shut down that specific port â' does this idea make sense' Iâ'm not having
> much luck implementing itâ?¦.
>
> "Tibor Karaszi" wrote:
>> Did you specify this new port in the connection string/server name?
>> It seems you just want to change the port, and for that you don't need to create a new endpoint.
>> You
>> would just change the port in Server Configuration program.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "adba" <adba@.discussions.microsoft.com> wrote in message
>> news:04055949-5892-425E-8593-541612857169@.microsoft.com...
>> >i have created a new tcp endpoint using the following:
>> > CREATE ENDPOINT [CustomConnection1]
>> > STATE = STARTED
>> > AS TCP
>> > (LISTENER_PORT = 1444, LISTENER_IP =ALL)
>> > FOR TSQL() ;
>> > And I got warning about permissions being revoked for the default tcp
>> > endpoint, but I was okay with that since I really wanted all users to go
>> > through this new endpoint I am creating.
>> > I then granted connect pemissions to a sql login (called test) to connect to
>> > this endpoint.
>> > I added the tcp port 1444 to the IPALL TCP Port list & restarted SQL.
>> > I tried connecting to the sql server with the test login & it gave me a
>> > login failed error 18456.
>> > i then granted connect permissions to public to the default tcp port & tried
>> > conecting with the test user & it connected successfully. So obviously it was
>> > connecting with the default tcp port & not the new port i defined as the
>> > endpoint. to be sure, i stopped the new endpoint & connected with the test
>> > login again & it still worked.
>> > So my question is - what am i doing wrong? i dont connections to go through
>> > the default tcp port , i want them to go through the port i define in the
>> > endpoint.
>> > also, how can i connect to a specific port through management studio?
>> >
>> > just fyi - i basically followed the instructions on this link:
>> > http://msdn2.microsoft.com/en-us/library/ms189310.aspx
>> >
>> > thanks in advance!
>> >
>> >
>>|||thanks. okay now i wa able to connect to a specific port through management
studio.
but my problem is that to be able to connect to that port, i have to have
connect permissions granted to public on the default tcp port. like when you
create a new endpointm it takes away the permissions on the deafult port. so
i i leave it like that, i cannot connect to the new endpoint i created. if i
grant permissions to public, it kind of defeats the purpose of creating new
endpoints for me.
so i guess my question is can we connect to a new endpoint without granting
connect permissions to public on the default tcp port?
"Tibor Karaszi" wrote:
> I understand about your usage scenario. Yes, that makes sense to me.
> To connect to so something else than the "default" port, the client uses below syntax for the server
> name. Note that you don't specify an instance name if it is a named instance:
> SERVERNAME,PORT#
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "adba" <adba@.discussions.microsoft.com> wrote in message
> news:C84D70CE-AEC4-48F3-B56E-86C264C85858@.microsoft.com...
> > No, I havenâ't specified this new port in my servername â' how do I do that in
> > management studio? Lets assume my server name is sqltest & the new port
> > number is 1443 & the endpoint is called custom1. should the servername be
> > sqltest:1443 ?
> >
> > What I want to do is create different tcp endpoints for different
> > applications. That way the default port 1433 would be used only by admins
> > internally & other users outside the firewall will connect through another
> > port (new endpoint) than 1433. That way if security is compromised with one
> > application, I donâ't have to shut the entire server down, I should be able to
> > shut down that specific port â' does this idea make sense' Iâ'm not having
> > much luck implementing itâ?¦.
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Did you specify this new port in the connection string/server name?
> >>
> >> It seems you just want to change the port, and for that you don't need to create a new endpoint.
> >> You
> >> would just change the port in Server Configuration program.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "adba" <adba@.discussions.microsoft.com> wrote in message
> >> news:04055949-5892-425E-8593-541612857169@.microsoft.com...
> >> >i have created a new tcp endpoint using the following:
> >> > CREATE ENDPOINT [CustomConnection1]
> >> > STATE = STARTED
> >> > AS TCP
> >> > (LISTENER_PORT = 1444, LISTENER_IP =ALL)
> >> > FOR TSQL() ;
> >> > And I got warning about permissions being revoked for the default tcp
> >> > endpoint, but I was okay with that since I really wanted all users to go
> >> > through this new endpoint I am creating.
> >> > I then granted connect pemissions to a sql login (called test) to connect to
> >> > this endpoint.
> >> > I added the tcp port 1444 to the IPALL TCP Port list & restarted SQL.
> >> > I tried connecting to the sql server with the test login & it gave me a
> >> > login failed error 18456.
> >> > i then granted connect permissions to public to the default tcp port & tried
> >> > conecting with the test user & it connected successfully. So obviously it was
> >> > connecting with the default tcp port & not the new port i defined as the
> >> > endpoint. to be sure, i stopped the new endpoint & connected with the test
> >> > login again & it still worked.
> >> > So my question is - what am i doing wrong? i dont connections to go through
> >> > the default tcp port , i want them to go through the port i define in the
> >> > endpoint.
> >> > also, how can i connect to a specific port through management studio?
> >> >
> >> > just fyi - i basically followed the instructions on this link:
> >> > http://msdn2.microsoft.com/en-us/library/ms189310.aspx
> >> >
> >> > thanks in advance!
> >> >
> >> >
> >>
> >>
>|||> but my problem is that to be able to connect to that port, i have to have
> connect permissions granted to public on the default tcp port.
That is not what I'm seeing. Below is what I did, and result of my actions. I'm on sp2, btw:
CREATE LOGIN adba WITH PASSWORD = 'pwd'
CREATE ENDPOINT [CustomConnection1]
STATE = STARTED
AS TCP
(LISTENER_PORT = 1444, LISTENER_IP =ALL)
FOR TSQL() ;
GRANT CONNECT ON ENDPOINT::[CustomConnection1] to [public]
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
-- Added port 1444 in Network Server Utility, IPAll, TCP Port: 1433,1444
-- Restarted SQL Server
REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
--Login adba able to login on port 1444
--Login adba not able to login with only server name specified
--Login adba not able to login on port 1433
--Windows login able to login on port 1433
--Windows login able to login with only server name specified
--(my Windows account is a login member of syadmin)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"adba" <adba@.discussions.microsoft.com> wrote in message
news:9AF19309-A371-4983-B788-95E67789552C@.microsoft.com...
> thanks. okay now i wa able to connect to a specific port through management
> studio.
> but my problem is that to be able to connect to that port, i have to have
> connect permissions granted to public on the default tcp port. like when you
> create a new endpointm it takes away the permissions on the deafult port. so
> i i leave it like that, i cannot connect to the new endpoint i created. if i
> grant permissions to public, it kind of defeats the purpose of creating new
> endpoints for me.
> so i guess my question is can we connect to a new endpoint without granting
> connect permissions to public on the default tcp port?
> "Tibor Karaszi" wrote:
>> I understand about your usage scenario. Yes, that makes sense to me.
>> To connect to so something else than the "default" port, the client uses below syntax for the
>> server
>> name. Note that you don't specify an instance name if it is a named instance:
>> SERVERNAME,PORT#
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "adba" <adba@.discussions.microsoft.com> wrote in message
>> news:C84D70CE-AEC4-48F3-B56E-86C264C85858@.microsoft.com...
>> > No, I havenâ't specified this new port in my servername â' how do I do that in
>> > management studio? Lets assume my server name is sqltest & the new port
>> > number is 1443 & the endpoint is called custom1. should the servername be
>> > sqltest:1443 ?
>> >
>> > What I want to do is create different tcp endpoints for different
>> > applications. That way the default port 1433 would be used only by admins
>> > internally & other users outside the firewall will connect through another
>> > port (new endpoint) than 1433. That way if security is compromised with one
>> > application, I donâ't have to shut the entire server down, I should be able to
>> > shut down that specific port â' does this idea make sense' Iâ'm not having
>> > much luck implementing itâ?¦.
>> >
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Did you specify this new port in the connection string/server name?
>> >>
>> >> It seems you just want to change the port, and for that you don't need to create a new
>> >> endpoint.
>> >> You
>> >> would just change the port in Server Configuration program.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "adba" <adba@.discussions.microsoft.com> wrote in message
>> >> news:04055949-5892-425E-8593-541612857169@.microsoft.com...
>> >> >i have created a new tcp endpoint using the following:
>> >> > CREATE ENDPOINT [CustomConnection1]
>> >> > STATE = STARTED
>> >> > AS TCP
>> >> > (LISTENER_PORT = 1444, LISTENER_IP =ALL)
>> >> > FOR TSQL() ;
>> >> > And I got warning about permissions being revoked for the default tcp
>> >> > endpoint, but I was okay with that since I really wanted all users to go
>> >> > through this new endpoint I am creating.
>> >> > I then granted connect pemissions to a sql login (called test) to connect to
>> >> > this endpoint.
>> >> > I added the tcp port 1444 to the IPALL TCP Port list & restarted SQL.
>> >> > I tried connecting to the sql server with the test login & it gave me a
>> >> > login failed error 18456.
>> >> > i then granted connect permissions to public to the default tcp port & tried
>> >> > conecting with the test user & it connected successfully. So obviously it was
>> >> > connecting with the default tcp port & not the new port i defined as the
>> >> > endpoint. to be sure, i stopped the new endpoint & connected with the test
>> >> > login again & it still worked.
>> >> > So my question is - what am i doing wrong? i dont connections to go through
>> >> > the default tcp port , i want them to go through the port i define in the
>> >> > endpoint.
>> >> > also, how can i connect to a specific port through management studio?
>> >> >
>> >> > just fyi - i basically followed the instructions on this link:
>> >> > http://msdn2.microsoft.com/en-us/library/ms189310.aspx
>> >> >
>> >> > thanks in advance!
>> >> >
>> >> >
>> >>
>> >>
>>|||ok i tried your steps like u mentioned them & all of them worked except for
"Login adba not able to login with only server name specified". The login
adba is still able to connect with the servername :(
Public has grant permissions to the following:
CustomConnection1
TSQL Default VIA
TSQL Local Machine
TSQL Named Pipes
So I'm guessing adba is able to connect usng one of the other protocols'
"Tibor Karaszi" wrote:
> > but my problem is that to be able to connect to that port, i have to have
> > connect permissions granted to public on the default tcp port.
> That is not what I'm seeing. Below is what I did, and result of my actions. I'm on sp2, btw:
> CREATE LOGIN adba WITH PASSWORD = 'pwd'
> CREATE ENDPOINT [CustomConnection1]
> STATE = STARTED
> AS TCP
> (LISTENER_PORT = 1444, LISTENER_IP =ALL)
> FOR TSQL() ;
> GRANT CONNECT ON ENDPOINT::[CustomConnection1] to [public]
> GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
> -- Added port 1444 in Network Server Utility, IPAll, TCP Port: 1433,1444
> -- Restarted SQL Server
> REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
> --Login adba able to login on port 1444
> --Login adba not able to login with only server name specified
> --Login adba not able to login on port 1433
> --Windows login able to login on port 1433
> --Windows login able to login with only server name specified
> --(my Windows account is a login member of syadmin)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "adba" <adba@.discussions.microsoft.com> wrote in message
> news:9AF19309-A371-4983-B788-95E67789552C@.microsoft.com...
> > thanks. okay now i wa able to connect to a specific port through management
> > studio.
> > but my problem is that to be able to connect to that port, i have to have
> > connect permissions granted to public on the default tcp port. like when you
> > create a new endpointm it takes away the permissions on the deafult port. so
> > i i leave it like that, i cannot connect to the new endpoint i created. if i
> > grant permissions to public, it kind of defeats the purpose of creating new
> > endpoints for me.
> > so i guess my question is can we connect to a new endpoint without granting
> > connect permissions to public on the default tcp port?
> >
> > "Tibor Karaszi" wrote:
> >
> >> I understand about your usage scenario. Yes, that makes sense to me.
> >> To connect to so something else than the "default" port, the client uses below syntax for the
> >> server
> >> name. Note that you don't specify an instance name if it is a named instance:
> >> SERVERNAME,PORT#
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "adba" <adba@.discussions.microsoft.com> wrote in message
> >> news:C84D70CE-AEC4-48F3-B56E-86C264C85858@.microsoft.com...
> >> > No, I havenâ't specified this new port in my servername â' how do I do that in
> >> > management studio? Lets assume my server name is sqltest & the new port
> >> > number is 1443 & the endpoint is called custom1. should the servername be
> >> > sqltest:1443 ?
> >> >
> >> > What I want to do is create different tcp endpoints for different
> >> > applications. That way the default port 1433 would be used only by admins
> >> > internally & other users outside the firewall will connect through another
> >> > port (new endpoint) than 1433. That way if security is compromised with one
> >> > application, I donâ't have to shut the entire server down, I should be able to
> >> > shut down that specific port â' does this idea make sense' Iâ'm not having
> >> > much luck implementing itâ?¦.
> >> >
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Did you specify this new port in the connection string/server name?
> >> >>
> >> >> It seems you just want to change the port, and for that you don't need to create a new
> >> >> endpoint.
> >> >> You
> >> >> would just change the port in Server Configuration program.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "adba" <adba@.discussions.microsoft.com> wrote in message
> >> >> news:04055949-5892-425E-8593-541612857169@.microsoft.com...
> >> >> >i have created a new tcp endpoint using the following:
> >> >> > CREATE ENDPOINT [CustomConnection1]
> >> >> > STATE = STARTED
> >> >> > AS TCP
> >> >> > (LISTENER_PORT = 1444, LISTENER_IP =ALL)
> >> >> > FOR TSQL() ;
> >> >> > And I got warning about permissions being revoked for the default tcp
> >> >> > endpoint, but I was okay with that since I really wanted all users to go
> >> >> > through this new endpoint I am creating.
> >> >> > I then granted connect pemissions to a sql login (called test) to connect to
> >> >> > this endpoint.
> >> >> > I added the tcp port 1444 to the IPALL TCP Port list & restarted SQL.
> >> >> > I tried connecting to the sql server with the test login & it gave me a
> >> >> > login failed error 18456.
> >> >> > i then granted connect permissions to public to the default tcp port & tried
> >> >> > conecting with the test user & it connected successfully. So obviously it was
> >> >> > connecting with the default tcp port & not the new port i defined as the
> >> >> > endpoint. to be sure, i stopped the new endpoint & connected with the test
> >> >> > login again & it still worked.
> >> >> > So my question is - what am i doing wrong? i dont connections to go through
> >> >> > the default tcp port , i want them to go through the port i define in the
> >> >> > endpoint.
> >> >> > also, how can i connect to a specific port through management studio?
> >> >> >
> >> >> > just fyi - i basically followed the instructions on this link:
> >> >> > http://msdn2.microsoft.com/en-us/library/ms189310.aspx
> >> >> >
> >> >> > thanks in advance!
> >> >> >
> >> >> >
> >> >>
> >> >>
> >>
> >>
>|||> So I'm guessing adba is able to connect usng one of the other protocols'
That seems to be the case. You can check what netlib a connection is using though the catalog views.
And you can disable netlibs using Configuration Manager. Note that the VIA is disabled by default
and Local Machine is only for local access, so the only one you would need to disable is Named
Pipes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"adba" <adba@.discussions.microsoft.com> wrote in message
news:DF4D5051-2D13-4083-9A82-BCC4A597E071@.microsoft.com...
> ok i tried your steps like u mentioned them & all of them worked except for
> "Login adba not able to login with only server name specified". The login
> adba is still able to connect with the servername :(
> Public has grant permissions to the following:
> CustomConnection1
> TSQL Default VIA
> TSQL Local Machine
> TSQL Named Pipes
> So I'm guessing adba is able to connect usng one of the other protocols'
>
> "Tibor Karaszi" wrote:
>> > but my problem is that to be able to connect to that port, i have to have
>> > connect permissions granted to public on the default tcp port.
>> That is not what I'm seeing. Below is what I did, and result of my actions. I'm on sp2, btw:
>> CREATE LOGIN adba WITH PASSWORD = 'pwd'
>> CREATE ENDPOINT [CustomConnection1]
>> STATE = STARTED
>> AS TCP
>> (LISTENER_PORT = 1444, LISTENER_IP =ALL)
>> FOR TSQL() ;
>> GRANT CONNECT ON ENDPOINT::[CustomConnection1] to [public]
>> GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
>> -- Added port 1444 in Network Server Utility, IPAll, TCP Port: 1433,1444
>> -- Restarted SQL Server
>> REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
>> --Login adba able to login on port 1444
>> --Login adba not able to login with only server name specified
>> --Login adba not able to login on port 1433
>> --Windows login able to login on port 1433
>> --Windows login able to login with only server name specified
>> --(my Windows account is a login member of syadmin)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "adba" <adba@.discussions.microsoft.com> wrote in message
>> news:9AF19309-A371-4983-B788-95E67789552C@.microsoft.com...
>> > thanks. okay now i wa able to connect to a specific port through management
>> > studio.
>> > but my problem is that to be able to connect to that port, i have to have
>> > connect permissions granted to public on the default tcp port. like when you
>> > create a new endpointm it takes away the permissions on the deafult port. so
>> > i i leave it like that, i cannot connect to the new endpoint i created. if i
>> > grant permissions to public, it kind of defeats the purpose of creating new
>> > endpoints for me.
>> > so i guess my question is can we connect to a new endpoint without granting
>> > connect permissions to public on the default tcp port?
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> I understand about your usage scenario. Yes, that makes sense to me.
>> >> To connect to so something else than the "default" port, the client uses below syntax for the
>> >> server
>> >> name. Note that you don't specify an instance name if it is a named instance:
>> >> SERVERNAME,PORT#
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "adba" <adba@.discussions.microsoft.com> wrote in message
>> >> news:C84D70CE-AEC4-48F3-B56E-86C264C85858@.microsoft.com...
>> >> > No, I havenâ't specified this new port in my servername â' how do I do that in
>> >> > management studio? Lets assume my server name is sqltest & the new port
>> >> > number is 1443 & the endpoint is called custom1. should the servername be
>> >> > sqltest:1443 ?
>> >> >
>> >> > What I want to do is create different tcp endpoints for different
>> >> > applications. That way the default port 1433 would be used only by admins
>> >> > internally & other users outside the firewall will connect through another
>> >> > port (new endpoint) than 1433. That way if security is compromised with one
>> >> > application, I donâ't have to shut the entire server down, I should be able to
>> >> > shut down that specific port â' does this idea make sense' Iâ'm not having
>> >> > much luck implementing itâ?¦.
>> >> >
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Did you specify this new port in the connection string/server name?
>> >> >>
>> >> >> It seems you just want to change the port, and for that you don't need to create a new
>> >> >> endpoint.
>> >> >> You
>> >> >> would just change the port in Server Configuration program.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "adba" <adba@.discussions.microsoft.com> wrote in message
>> >> >> news:04055949-5892-425E-8593-541612857169@.microsoft.com...
>> >> >> >i have created a new tcp endpoint using the following:
>> >> >> > CREATE ENDPOINT [CustomConnection1]
>> >> >> > STATE = STARTED
>> >> >> > AS TCP
>> >> >> > (LISTENER_PORT = 1444, LISTENER_IP =ALL)
>> >> >> > FOR TSQL() ;
>> >> >> > And I got warning about permissions being revoked for the default tcp
>> >> >> > endpoint, but I was okay with that since I really wanted all users to go
>> >> >> > through this new endpoint I am creating.
>> >> >> > I then granted connect pemissions to a sql login (called test) to connect to
>> >> >> > this endpoint.
>> >> >> > I added the tcp port 1444 to the IPALL TCP Port list & restarted SQL.
>> >> >> > I tried connecting to the sql server with the test login & it gave me a
>> >> >> > login failed error 18456.
>> >> >> > i then granted connect permissions to public to the default tcp port & tried
>> >> >> > conecting with the test user & it connected successfully. So obviously it was
>> >> >> > connecting with the default tcp port & not the new port i defined as the
>> >> >> > endpoint. to be sure, i stopped the new endpoint & connected with the test
>> >> >> > login again & it still worked.
>> >> >> > So my question is - what am i doing wrong? i dont connections to go through
>> >> >> > the default tcp port , i want them to go through the port i define in the
>> >> >> > endpoint.
>> >> >> > also, how can i connect to a specific port through management studio?
>> >> >> >
>> >> >> > just fyi - i basically followed the instructions on this link:
>> >> >> > http://msdn2.microsoft.com/en-us/library/ms189310.aspx
>> >> >> >
>> >> >> > thanks in advance!
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >>
>> >>
>>|||That was it Tibor. Once I disabled the other protocols it couldnt connect.
Thanks so much for your help.
"Tibor Karaszi" wrote:
> > So I'm guessing adba is able to connect usng one of the other protocols'
> That seems to be the case. You can check what netlib a connection is using though the catalog views.
> And you can disable netlibs using Configuration Manager. Note that the VIA is disabled by default
> and Local Machine is only for local access, so the only one you would need to disable is Named
> Pipes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "adba" <adba@.discussions.microsoft.com> wrote in message
> news:DF4D5051-2D13-4083-9A82-BCC4A597E071@.microsoft.com...
> > ok i tried your steps like u mentioned them & all of them worked except for
> >
> > "Login adba not able to login with only server name specified". The login
> > adba is still able to connect with the servername :(
> >
> > Public has grant permissions to the following:
> > CustomConnection1
> > TSQL Default VIA
> > TSQL Local Machine
> > TSQL Named Pipes
> >
> > So I'm guessing adba is able to connect usng one of the other protocols'
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> > but my problem is that to be able to connect to that port, i have to have
> >> > connect permissions granted to public on the default tcp port.
> >>
> >> That is not what I'm seeing. Below is what I did, and result of my actions. I'm on sp2, btw:
> >>
> >> CREATE LOGIN adba WITH PASSWORD = 'pwd'
> >>
> >> CREATE ENDPOINT [CustomConnection1]
> >> STATE = STARTED
> >> AS TCP
> >> (LISTENER_PORT = 1444, LISTENER_IP =ALL)
> >> FOR TSQL() ;
> >>
> >> GRANT CONNECT ON ENDPOINT::[CustomConnection1] to [public]
> >>
> >> GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
> >>
> >> -- Added port 1444 in Network Server Utility, IPAll, TCP Port: 1433,1444
> >> -- Restarted SQL Server
> >>
> >> REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]
> >>
> >> --Login adba able to login on port 1444
> >> --Login adba not able to login with only server name specified
> >> --Login adba not able to login on port 1433
> >> --Windows login able to login on port 1433
> >> --Windows login able to login with only server name specified
> >> --(my Windows account is a login member of syadmin)
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "adba" <adba@.discussions.microsoft.com> wrote in message
> >> news:9AF19309-A371-4983-B788-95E67789552C@.microsoft.com...
> >> > thanks. okay now i wa able to connect to a specific port through management
> >> > studio.
> >> > but my problem is that to be able to connect to that port, i have to have
> >> > connect permissions granted to public on the default tcp port. like when you
> >> > create a new endpointm it takes away the permissions on the deafult port. so
> >> > i i leave it like that, i cannot connect to the new endpoint i created. if i
> >> > grant permissions to public, it kind of defeats the purpose of creating new
> >> > endpoints for me.
> >> > so i guess my question is can we connect to a new endpoint without granting
> >> > connect permissions to public on the default tcp port?
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> I understand about your usage scenario. Yes, that makes sense to me.
> >> >> To connect to so something else than the "default" port, the client uses below syntax for the
> >> >> server
> >> >> name. Note that you don't specify an instance name if it is a named instance:
> >> >> SERVERNAME,PORT#
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "adba" <adba@.discussions.microsoft.com> wrote in message
> >> >> news:C84D70CE-AEC4-48F3-B56E-86C264C85858@.microsoft.com...
> >> >> > No, I havenâ't specified this new port in my servername â' how do I do that in
> >> >> > management studio? Lets assume my server name is sqltest & the new port
> >> >> > number is 1443 & the endpoint is called custom1. should the servername be
> >> >> > sqltest:1443 ?
> >> >> >
> >> >> > What I want to do is create different tcp endpoints for different
> >> >> > applications. That way the default port 1433 would be used only by admins
> >> >> > internally & other users outside the firewall will connect through another
> >> >> > port (new endpoint) than 1433. That way if security is compromised with one
> >> >> > application, I donâ't have to shut the entire server down, I should be able to
> >> >> > shut down that specific port â' does this idea make sense' Iâ'm not having
> >> >> > much luck implementing itâ?¦.
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> Did you specify this new port in the connection string/server name?
> >> >> >>
> >> >> >> It seems you just want to change the port, and for that you don't need to create a new
> >> >> >> endpoint.
> >> >> >> You
> >> >> >> would just change the port in Server Configuration program.
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >> >>
> >> >> >>
> >> >> >> "adba" <adba@.discussions.microsoft.com> wrote in message
> >> >> >> news:04055949-5892-425E-8593-541612857169@.microsoft.com...
> >> >> >> >i have created a new tcp endpoint using the following:
> >> >> >> > CREATE ENDPOINT [CustomConnection1]
> >> >> >> > STATE = STARTED
> >> >> >> > AS TCP
> >> >> >> > (LISTENER_PORT = 1444, LISTENER_IP =ALL)
> >> >> >> > FOR TSQL() ;
> >> >> >> > And I got warning about permissions being revoked for the default tcp
> >> >> >> > endpoint, but I was okay with that since I really wanted all users to go
> >> >> >> > through this new endpoint I am creating.
> >> >> >> > I then granted connect pemissions to a sql login (called test) to connect to
> >> >> >> > this endpoint.
> >> >> >> > I added the tcp port 1444 to the IPALL TCP Port list & restarted SQL.
> >> >> >> > I tried connecting to the sql server with the test login & it gave me a
> >> >> >> > login failed error 18456.
> >> >> >> > i then granted connect permissions to public to the default tcp port & tried
> >> >> >> > conecting with the test user & it connected successfully. So obviously it was
> >> >> >> > connecting with the default tcp port & not the new port i defined as the
> >> >> >> > endpoint. to be sure, i stopped the new endpoint & connected with the test
> >> >> >> > login again & it still worked.
> >> >> >> > So my question is - what am i doing wrong? i dont connections to go through
> >> >> >> > the default tcp port , i want them to go through the port i define in the
> >> >> >> > endpoint.
> >> >> >> > also, how can i connect to a specific port through management studio?
> >> >> >> >
> >> >> >> > just fyi - i basically followed the instructions on this link:
> >> >> >> > http://msdn2.microsoft.com/en-us/library/ms189310.aspx
> >> >> >> >
> >> >> >> > thanks in advance!
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>

Connect to a server from client

I'm receiving ths following error message when I try to connect to my server:
Cannot generate SSPI context. (.Net SqlClient Data Provider)
An idea?
Check this article:
http://blogs.msdn.com/sql_protocols/archive/2005/10/19/482782.aspx
Ayad Shammout
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:C22E4CA3-2289-47B0-B009-C7D63E590B78@.microsoft.com...
> I'm receiving ths following error message when I try to connect to my
> server:
> Cannot generate SSPI context. (.Net SqlClient Data Provider)
> An idea?

Monday, 19 March 2012

CONNECT TO

Hello,
I have 3 registrations under a Server Group. They are all connected using
Windows NT. I was trying to use the following in SQL Query Analyzer to
execute a stored procedure that was in another registration I got the
following code from books on line:
EXEC SQL CONNECT TO "Reg2.Funds";
However, I get the error: Incorrect syntax near the keyword 'TO'.
Any help with this would be appreciated.
--
Thanks in advance,
sck10Thats for embedded SQL in C.
If you want to in SQL Server you can go by the four part name:
example:
Select * from [servername].[databasename].[owner].[objectname]
where servername can be left out if you are not using a linked server, if
you want to use a linked server (connect to any other SQL Server than the
actual one, or even another instance) you have to conigure that but adding a
new linked server.
HTH, Jens Suessmeyer.
"sck10" <sck10@.online.nospam> wrote in message
news:O9rgf2YiFHA.1480@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have 3 registrations under a Server Group. They are all connected using
> Windows NT. I was trying to use the following in SQL Query Analyzer to
> execute a stored procedure that was in another registration I got the
> following code from books on line:
> EXEC SQL CONNECT TO "Reg2.Funds";
> However, I get the error: Incorrect syntax near the keyword 'TO'.
> Any help with this would be appreciated.
> --
> Thanks in advance,
> sck10
>|||Hi sck10,
Thanks for your posting!
Based on my knowledge, CONNECT TO is embedded SQL for C. You cannot use it
in Query Analyzer directly. To call another SQL Server's stored procedure,
add another SQL Server as linked server and then call using the four parts
naming: [Server].[Database].[Owner].[Object]
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

CONNECT TO

Hello,
I have 3 registrations under a Server Group. They are all connected using
Windows NT. I was trying to use the following in SQL Query Analyzer to
execute a stored procedure that was in another registration I got the
following code from books on line:
EXEC SQL CONNECT TO "Reg2.Funds";
However, I get the error: Incorrect syntax near the keyword 'TO'.
Any help with this would be appreciated.
Thanks in advance,
sck10
Thats for embedded SQL in C.
If you want to in SQL Server you can go by the four part name:
example:
Select * from [servername].[databasename].[owner].[objectname]
where servername can be left out if you are not using a linked server, if
you want to use a linked server (connect to any other SQL Server than the
actual one, or even another instance) you have to conigure that but adding a
new linked server.
HTH, Jens Suessmeyer.
"sck10" <sck10@.online.nospam> wrote in message
news:O9rgf2YiFHA.1480@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have 3 registrations under a Server Group. They are all connected using
> Windows NT. I was trying to use the following in SQL Query Analyzer to
> execute a stored procedure that was in another registration I got the
> following code from books on line:
> EXEC SQL CONNECT TO "Reg2.Funds";
> However, I get the error: Incorrect syntax near the keyword 'TO'.
> Any help with this would be appreciated.
> --
> Thanks in advance,
> sck10
>
|||Hi sck10,
Thanks for your posting!
Based on my knowledge, CONNECT TO is embedded SQL for C. You cannot use it
in Query Analyzer directly. To call another SQL Server's stored procedure,
add another SQL Server as linked server and then call using the four parts
naming: [Server].[Database].[Owner].[Object]
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

CONNECT TO

Hello,
I have 3 registrations under a Server Group. They are all connected using
Windows NT. I was trying to use the following in SQL Query Analyzer to
execute a stored procedure that was in another registration I got the
following code from books on line:
EXEC SQL CONNECT TO "Reg2.Funds";
However, I get the error: Incorrect syntax near the keyword 'TO'.
Any help with this would be appreciated.
Thanks in advance,
sck10Thats for embedded SQL in C.
If you want to in SQL Server you can go by the four part name:
example:
Select * from [servername].[databasename].[owner].[objectnam
e]
where servername can be left out if you are not using a linked server, if
you want to use a linked server (connect to any other SQL Server than the
actual one, or even another instance) you have to conigure that but adding a
new linked server.
HTH, Jens Suessmeyer.
"sck10" <sck10@.online.nospam> wrote in message
news:O9rgf2YiFHA.1480@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have 3 registrations under a Server Group. They are all connected using
> Windows NT. I was trying to use the following in SQL Query Analyzer to
> execute a stored procedure that was in another registration I got the
> following code from books on line:
> EXEC SQL CONNECT TO "Reg2.Funds";
> However, I get the error: Incorrect syntax near the keyword 'TO'.
> Any help with this would be appreciated.
> --
> Thanks in advance,
> sck10
>|||Hi sck10,
Thanks for your posting!
Based on my knowledge, CONNECT TO is embedded SQL for C. You cannot use it
in Query Analyzer directly. To call another SQL Server's stored procedure,
add another SQL Server as linked server and then call using the four parts
naming: [Server].[Database].[Owner].[Object]
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Sunday, 11 March 2012

Connect Delphi 2006 to MS SQL Express

I use Delphi 2006 to connect MS SQL Express 2005. When I save record, it shows the following error message:

Database Server Error : Cannot create new transaction because capacity was exceeded.

Does anyone know what happen and how to solve?

Thanks!

A quick MSN Search finds http://bdn.borland.com/article/0,1410,32020,00.html with the following solution:

When using dbExpress MSSQL and TClientDataSet.ApplyUpdates,
you may encounter either of the following errors while
applying updates, due to a SQLOLEDB.DLL limitation:

"Cannot create new transaction because capacity was exceeded."

"Cannot create new connection because in manual or
distributed transaction mode."

To resolve the errors, use one of the following workarounds:

- On the DataSetProvider Options property, make sure
poFetchBlobsOnDemand and poFetchDetailsOnDemand are False.

- Use a BeforeApplyUpdates event to either call CloseDataSets
or Close the connection:

procedure TForm1.DataSetProvider1BeforeApplyUpdates(Sender: TObject;
var OwnerData: OleVariant);
begin
SQLConnection1.CloseDataSets;
// SQLConnection1.Close;
end;

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

Thursday, 8 March 2012

conn string

What is the impact of the differences in the following 2 connection strings:

Initial Catalog=xxx;Data Source=xxxxxx;Trusted_Connection=True;

Driver={SQL Server};Server=xxxxx;database=xxx;Trusted_Connection=yes;

To outline the differences I'm concerned with are

Missing Driver on first string

Difference in Trusted_Connection parameter (true vs. yes)

Initial Catalog vs. database parameter

See my response in the [SQL Server Data Access] forum.

You may also find this url a useful resource: www.connectionstrings.com

Confussions

I would like to know what a computer with these features mean. This computer has the following features:

Solaris
4GL OpenINGRES
HTML+Java over OpenINGRES

Because Im not a person that dominates the databases world these are my guesses to what I think it means (please correct me if Im wrong):

Its a computer that mantains a database.

I think that "Solaris" is the OS where the database is located. Solaris is also an UNIX OS. Is it true?.

I also think that "4GL OpenINGRES" means that their database is OpenINGRES (BTW, which is the company that have made OpenIngres?, Informix?) and that the language that they used to build the database was a 4GL (which I think is that is some kind of SQL language. Is it very different from the normal SQL?. Do you know what are the main differences between 4GL and SQL or do you know of any webpage that tells what are the commands or syntax for 4GL?.

Finally, I think that "HTML+Java over OpenINGRES" means that someone that wants to get any information from the database (OpenINGRES) from the internet uses a combination of HTML and Java code to access the database. For example if I was to access some data from the database I would have to login to a webpage where it asks me for a "username" and a "password". This webpages interacts with the database with HTML and Java. Is it true?.

And the last question. If someone was trying to enter to the database what should he do?:
SQL injection?. Im not sure if this will work if they have the 4GL, although I know that the 4GL is also some kind of SQL. Is it possible to do SQL injection with the features of the computer that I have told you. Or it will have to be a "4GL injection"?. I dont know if "4GL injection" exists but it is just to make me an idea of the possibilities for someone to use "SQL injection" on the database.

English is not my first language so maybe I havent explained me well. If you want me to give more explanataions just ask me for it. I really need to know it.

Thank you in advance.Seems like you have the bones of it there already, howver, 4GL is not a language as such it is known as Fourth Generation Language, MSAccess as example.
User friendly coding like Sql...

Wednesday, 7 March 2012

Confusion about dialog security steps.

Hi There

I have done the following.

2 Servers across the net work Server A and Server B.

I have created private keys at each and copied to public keys across.

I create an endpoint at each with validation by the certificates. I then create appropriate users and logins at each and import the public certificates with authorization to the users who have send permission on the endpoints.

That is transport security sorted and it works.

Now from what i have learnt from examples, to setup dialog security i do the following.

create a private key in each DB :

create certificate Store001DialogPri

with subject = 'Store001DialogPri',

start_date = '07/20/2006'

active for begin_dialog = on;

I then copy the public key to each server, create a user only in each DB and import the public keys with authorization on the user. And grant send to the appropriate servcies to the user.

I then create appropriate remote service bindings with this user.

Now this works for me. Everythign seems A OK.

However i am going thorugh the Service Broker "bible", and there are a ton of steps i am not doing but my setup works ?

Steps i am not doing for example is when i create the private keys in the DB i should authorize them to a user i create who is then gratnted CONTROL permission on the SERVICES.

Now i do not do this but everything seems to be working. I thought i finally understood dialogs security but now i am totally confused ?

Is what i am doing correct ? If so why are these all these additinal steps mentioned ? WHat am i missing ?

Thanx

The AUTHORIZATION and CONTROL steps in the samples are needed if you are performing these steps for somebody else. You test probably ended up with all the services being owned by [dbo], (you are sysadmin on the test machine, aren't you?), and the certificates with private keys also being owned by [dbo], so it just worked. But in real life, you might have to do this setup steps for a service owned by a different database user, so in that case is important to associate the private key with the service owner.

HTH,
~ Remus

|||

Hi Remus

Yes everything should be owned by dbo, however i do not see any otehr users coming into the SB application, so therefore i shouldnot have to do these steps?

Thanx for the feedback

Confusing CONTAINS behavior

I'm experimenting with the CONTAINS operation and I've come across some
confusing or inconsistent results. The following results are exactly the
same if the search terms are enclosed in double quotes so that they are
treated as a phrase.
I'm searching a Books table for books whose Author column contains "cs".
The following CONTAINS statements in the WHERE clause result in books by
"Lewis, C.S." being returned:
CONTAINS( Author, 'cs' )
CONTAINS( Author, 'CS' )
CONTAINS( Author, 'C.S.' )
The fact that "cs" matches an author containing "C.S." indicates that both
case and punctuation are ignored. However, the following CONTAINS statement
does not return any results for "Lewis, C.S.":
CONTAINS( Author, 'c.s.' )
This puzzles me. It seems as though either punctuation is ignored or case
is ignored, but not both. What kind of behavior is that? Is this by design
?Greg Smalter wrote:
> I'm experimenting with the CONTAINS operation and I've come across
> some confusing or inconsistent results. The following results are
> exactly the same if the search terms are enclosed in double quotes so
> that they are treated as a phrase.
> I'm searching a Books table for books whose Author column contains
> "cs".
> The following CONTAINS statements in the WHERE clause result in books
> by "Lewis, C.S." being returned:
> CONTAINS( Author, 'cs' )
> CONTAINS( Author, 'CS' )
> CONTAINS( Author, 'C.S.' )
> The fact that "cs" matches an author containing "C.S." indicates that
> both case and punctuation are ignored. However, the following
> CONTAINS statement does not return any results for "Lewis, C.S.":
> CONTAINS( Author, 'c.s.' )
> This puzzles me. It seems as though either punctuation is ignored or
> case is ignored, but not both. What kind of behavior is that? Is
> this by design?
According to BOL, a word is one or more characters without spaces or
punctuation. Punctuation marks such as the period, colon, semicolon,
comma, and hyphen are ignored during a search.
I do not believe the full-text engine is indexing the punctuation marks
which is why the second query fails.
David Gugick
Quest Software
www.quest.com|||I agree about the specification of the word and how it shouldn't contain
punctuation. That is why I also tested it with phrases surrounded by double
quotes, where the specification doesn't mention that puncutation is not
allowed.
I imagine you are right about the indexing, but that doesn't explain why
CONTAINS( Author, 'C.S.' ) works while CONTAINS( Author, 'c.s.' ) does not
work, because they both have the exact same punctuation. The only differenc
e
is case.
"David Gugick" wrote:

> Greg Smalter wrote:
> According to BOL, a word is one or more characters without spaces or
> punctuation. Punctuation marks such as the period, colon, semicolon,
> comma, and hyphen are ignored during a search.
> I do not believe the full-text engine is indexing the punctuation marks
> which is why the second query fails.
>
> --
> David Gugick
> Quest Software
> www.quest.com
>|||Greg Smalter wrote:
> I agree about the specification of the word and how it shouldn't
> contain punctuation. That is why I also tested it with phrases
> surrounded by double quotes, where the specification doesn't mention
> that puncutation is not allowed.
> I imagine you are right about the indexing, but that doesn't explain
> why CONTAINS( Author, 'C.S.' ) works while CONTAINS( Author, 'c.s.'
> ) does not work, because they both have the exact same punctuation.
> The only difference is case.
That part is confusing...
David Gugick
Quest Software
www.quest.com

Saturday, 25 February 2012

confused about pivot

I need to transform the following layout by pivoting, but am confused ......I have a compound primary key that I want to keep intact but then values in the row need to be broken out into their own row.

I need to go from this...

PKcol1 PKcol2 PKcol3 col4 col5 col6 col7

A 2007 1 Y N N N

A 2007 2 Y Y N N

A 2007 3 N N N Y

into this....

A 2007 1 col4 Y

A 2007 1 col5 N

A 2007 1 col6 N

A 2007 1 col7 N

A 2007 2 col4 Y

A 2007 2 col5 Y

A 2007 2 col6 N

A 2007 2 col7 N

A 2007 3 col4 N

A 2007 3 col5 N

A 2007 3 col6 N

A 2007 3 col7 Y

Can I do this using PIVOT or should I just do 4 inserts (one for each col40col7) into a temp table? Any suggestions?

Give a look to UNPIVOT in books online; this is an UNPIVOT and not a PIVOT

|||

Kent is right, you need to use UNPIVOT.

select

PKcol1,

PKcol2,

PKcol3,

col,

[value]

from

dbo.t1

unpivot

(

[value]

for col in ([col4], [col5], [col6], [col7])

) as unpvt;

AMB

Conflicts with Merge Replication

I am seeing the following conflict message in a merge replication
system.
Statler is the subscriber (also, remote distributor) and
EMGSQL1 is the publisher
The row was updated at Statler.Caryn but could not be updated
at emgsql1.caryn. Can not update Tlightbox because TlightboxFile
exists.
Column Conflight WinnerConflict Loser
lightbox_id19881988
member_id10531053
lb_nameCustomerCustwomer
create_dt5/13/2005 12:00:00 am5/13/2005 12:00:00 AM
last_mod_dt
expiration_dt8/11/2005 12:00:00 am8/11/2005 12:00:00 am
email_check{74434092-E95F-40B6-9211-99700DB
rowguidrowguid_valueidentical
the table definition for tlightbox is:
CREATE TABLE [dbo].[TLightBox] (
[lightbox_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[member_id] [int] NULL ,
[lb_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[create_dt] [datetime] NULL ,
[last_mod_dt] [datetime] NULL ,
[expiration_dt] [datetime] NULL ,
[email_check] [uniqueidentifier] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TLightboxFile] (
[lightbox_id] [int] NOT NULL ,
[modeltalent_id] [int] NOT NULL ,
[rank] [int] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
These tables are on both servers.
Any Ideas?
Thanks
Dave
what is the relationship between TLightBox and TLightBoxFile?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Gresham" <gresham@.panix.com> wrote in message
news:d62p39$lks$1@.reader1.panix.com...
> I am seeing the following conflict message in a merge replication
> system.
> Statler is the subscriber (also, remote distributor) and
> EMGSQL1 is the publisher
> The row was updated at Statler.Caryn but could not be updated
> at emgsql1.caryn. Can not update Tlightbox because TlightboxFile
> exists.
> Column Conflight Winner Conflict Loser
> lightbox_id 1988 1988
> member_id 1053 1053
> lb_name Customer Custwomer
> create_dt 5/13/2005 12:00:00 am 5/13/2005 12:00:00 AM
> last_mod_dt
> expiration_dt 8/11/2005 12:00:00 am 8/11/2005 12:00:00 am
> email_check {74434092-E95F-40B6-9211-99700DB
> rowguid rowguid_value identical
>
> the table definition for tlightbox is:
> CREATE TABLE [dbo].[TLightBox] (
> [lightbox_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
> [member_id] [int] NULL ,
> [lb_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [create_dt] [datetime] NULL ,
> [last_mod_dt] [datetime] NULL ,
> [expiration_dt] [datetime] NULL ,
> [email_check] [uniqueidentifier] NULL ,
> [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[TLightboxFile] (
> [lightbox_id] [int] NOT NULL ,
> [modeltalent_id] [int] NOT NULL ,
> [rank] [int] NULL ,
> [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
> ) ON [PRIMARY]
> GO
>
> These tables are on both servers.
>
> Any Ideas?
> Thanks
>
> Dave
>
|||In article <ek417yCWFHA.2960@.TK2MSFTNGP15.phx.gbl>,
Hilary Cotter <hilary.cotter@.gmail.com> wrote:
>what is the relationship between TLightBox and TLightBoxFile?
>
I just looked at the tables in question and there is no
defined releationship set between them. The only relationship
defined for TlightBox was for another table called TMember
with MemberID as the relationship column.

>"David Gresham" <gresham@.panix.com> wrote in message
>news:d62p39$lks$1@.reader1.panix.com...
>
|||Just seeing if I had given you all the info you needed for
this question. This database is a legacy database where
the original developers are no longer around. There
are no fk's that would join the two tables in question.
Sincerely,
Dave Gresham
In article <ek417yCWFHA.2960@.TK2MSFTNGP15.phx.gbl>,
Hilary Cotter <hilary.cotter@.gmail.com> wrote:
>what is the relationship between TLightBox and TLightBoxFile?
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>Looking for a FAQ on Indexing Services/SQL FTS
>http://www.indexserverfaq.com
>"David Gresham" <gresham@.panix.com> wrote in message
>news:d62p39$lks$1@.reader1.panix.com...
>
|||Your problem does not make a whole lot of sense. the best approach is to try
to repro it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Gresham" <gresham@.panix.com> wrote in message
news:d6algu$st6$1@.reader1.panix.com...
> Just seeing if I had given you all the info you needed for
> this question. This database is a legacy database where
> the original developers are no longer around. There
> are no fk's that would join the two tables in question.
> Sincerely,
>
> Dave Gresham
>
> In article <ek417yCWFHA.2960@.TK2MSFTNGP15.phx.gbl>,
> Hilary Cotter <hilary.cotter@.gmail.com> wrote:
>

Conflicts in Updateable Subscriptions

I've been experiencing conflicts in my replication system that I can't seem to get my head around. The following is the scenario:

3 sqlservers, all running sql server 2005. Server B is the publisher and Server A and Server C (64 bit) are subscribers. The Queue Reader Agent runs on the publisher. I set up transactional repl with updateable subscriptions with the default conflict resolution policy of 'Publisher wins'.

There are 2 kinds of processes: 1. Nightly batch updates and 2. Daytime updates by real clients. The Nightly batch updates runs an on the publisher, which is B. Batch updates are massive updates and running it on the publisher makes sense and it works like a charm. Online updates are made on the subscriber 'C'. This subscriber is set to Queued update mode, and everyday I see a significant number of transactions that are detected as conflicts and the Publisher wins. As a result the changes made on Server C are getting lost. I have verified that no user/client is logged into Server B to do any updates. Users complain that their updates are lost. This is the most puzzling and frustrating bit. I don't see how a conflict can happen if nobody is updating data on the Publisher during the day. SQL Updates on Server C are getting rolled back on a conflict detection because the "Publisher wins", and SQL Inserts on server C are getting deleted because they don't exist on the publisher. Now, how can a insert done on the subscriber be marked as a conflict. There is no row on the publisher to compare the unique guid with, how can it be a conflict?

And the Queue Reader Agent crashes every 3-4 days. No useful information except it creates a dump file for which users have no tools to read it.

Has anyone seen this behavior ? Or is there a known bug in the QueueReader Agent?

My users are losing faith in the replication system and so am I.

Thanks for your time,

-chiraj.

Hi,

Have you use the replication conflict viewer to see who is the winner of the conflict and what type is the winner transactions? It may be able to help you figure out where the conflict is from.

Peng

|||

Hi Peng,

Thank you for responding.

I looked at the conflict viewer, and the conflict winner is always the Publisher, and the conflct type is "Update conflict" WHen I click on any the rows in the conflict list, I get a popup error "Incorrect syntax near the keyword 'from', Error 21465, severity 16, state 1 raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage. (Microsoft SQL Server,Error: 156)". It pops up twice and when I get rid of them, the bottom pane of the viewer has the info: Queued update conflict. Publisher wins. Updating Subscriber. There are 3 columns: Column name, Conflict winner and Conflict loser. The Conflict winner column says "this row no longer exists in 'schema.tablename'". The conflict loser column has values for the table columns. I don't know the contents of the winner, maybe because of the earlier error with the popup message.

Any ideas?

Thanks.

|||

I did some digging and the popup error comes from sys.sp_MSgettrancftsrcrow procedure. Towards the end of the procedure, you have :

if (@.is_debug = 0)

begin

select @.cmd = N'select cmdtext from MSsrcrow_codetext order by step'

,@.dbname = db_name()

exec @.retcode = sys.xp_execresultset @.cmd, @.dbname

if (@.@.error != 0 or @.retcode != 0)

begin

raiserror(21465, 16, 1, 'xp_execresultset')

return (1)

end

else

I wonder why the message for #21465 is not installed.

I basically want to see and compare all the columns ( except timestamp ) from the subscriber and the publisher and see why there is a conflict.

I made sure the data was in sync this morning and then I start seeing conflicts during the day although no one is logged into the publisher. Beats me. Is there any trace flag I can turn on for replication to see what's going on?

-chiraj.

Conflicts in Updateable Subscriptions

I've been experiencing conflicts in my replication system that I can't seem to get my head around. The following is the scenario:

And the Queue Reader Agent crashes every 3-4 days. No useful information except it creates a dump file for which users have no tools to read it.

Has anyone seen this behavior ? Or is there a known bug in the QueueReader Agent?

My users are losing faith in the replication system and so am I.

Thanks for your time,

-chiraj.

Hi,

Have you use the replication conflict viewer to see who is the winner of the conflict and what type is the winner transactions? It may be able to help you figure out where the conflict is from.

Peng

|||

Hi Peng,

Thank you for responding.

Any ideas?

Thanks.

|||

I did some digging and the popup error comes from sys.sp_MSgettrancftsrcrow procedure. Towards the end of the procedure, you have :

if (@.is_debug = 0)

begin

select @.cmd = N'select cmdtext from MSsrcrow_codetext order by step'

,@.dbname = db_name()

exec @.retcode = sys.xp_execresultset @.cmd, @.dbname

if (@.@.error != 0 or @.retcode != 0)

begin

raiserror(21465, 16, 1, 'xp_execresultset')

return (1)

end

else

I wonder why the message for #21465 is not installed.

I basically want to see and compare all the columns ( except timestamp ) from the subscriber and the publisher and see why there is a conflict.

-chiraj.

Sunday, 25 March 2012

connect to specific session using AMO?

Connect to remote RS in SSMS

Connect to remote RS in SSMS

Connect to remote RS in SSMS

Connect to remote RS in SSMS

Connect To Oracle 10g 64 Bit using SQL Server Integration Services 2005 64 Bit

Tuesday, 20 March 2012

connect to a specific port on sql server/endpoint

Connect to a server from client

Monday, 19 March 2012

CONNECT TO

CONNECT TO

CONNECT TO

Sunday, 11 March 2012

Connect Delphi 2006 to MS SQL Express

Thursday, 8 March 2012

conn string

Confussions

Wednesday, 7 March 2012

Confusion about dialog security steps.

Confusing CONTAINS behavior

Saturday, 25 February 2012

confused about pivot

Conflicts with Merge Replication

Conflicts in Updateable Subscriptions

Conflicts in Updateable Subscriptions

SQL Configure

Blog Archive

About Me