3 server scenario problem

Hi
I'm having trouble connecting to an instance of SQL Server 2005 (Express)
from 2 individual Windows Server 2003 machines running IIS (asp.net 2.0)
I have an IIS on each 2k3 machine, both with identical sites. The idea is
simply that we have a third sql server which is accessed by these 2 websites.
One server gives me a Server login error (user not found:
MYDomain\ServerName), which the second server gives me the usual error 26 -
Cannot connect to Server/Instance specified.
Both IIS apps use the same query string (server=MYSQLSERVER\DARTS;
database=dartsV2;trustedconnection=yes;)
but as I say, one connects, one doesn't! very odd. Anyone got any
suggestions? I've enabled named pipes and tcp/ip on the sql server, along
with "allowing remote and local connections" using surface area config. I've
even setup SQL Browser, which seemed to cure one of the servers (both were
giving me the cannot find instance specified error).
Any suggestions guys?! I'm getting kinda desperate on this one... :o)
Cheers
Dan
Hi,
To narrow down this issue, I have serveral questions which need your
confirmation:
1. Could you ping the SQL Server successfully from the two clients?
2. What will happen if you explicitly add the two domain accounts to the
users group of your third server ?
Also, I recommend you try configuring the client settings as following:
Click the Start menu;
Type cliconfg.exe;
Check if TCP/IP and Named Pipes protocols are enabled;
Select the Alias tab;
Check if the server instance exists in the list;
If it's not existed, click the Add button;
Input the alias name (<servername>\SQLEXPRESS) for Named Pipes protocol and
click OK.
Apply the settings, and then try again.
If you want to use TCP/IP protocol, please first check the listening TCP
port on your server via SQL Server Configuration Manager.
Then add an alias for TCP/IP protocol at the clients. Note that you need to
cancel the selection of "Dynamically determin the port" and input your TCP
port number.
An alias can be different with your SQL Server instance name, but the
server name should be same as your server name.
Please ensure that you can use telnet to access your SQL Server TCP port
before configuring the alias for TCP/IP:
telnet <your server name> <port number>
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi Charles,
Thanks for the reply. I've actually fixed this particular issue, I simply
copied the code with the "user" error onto the other IIS server, so now I
have that error on both. However, I'll post somewhere else for that...
For clarification...
1) yes. I could also use osql (or sqlcmd) to connect to the sql server from
both domain servers, cos they both had sqle installed but no instance
running).
2) I tried that, but it couldn't find the computer accounts when I tried
adding roles etc - presumbly because they were computer accounts and not
users, I'm not sure though?
I have named pipes enabled but this is considered a security risk is it not?
However, I'm not sure how to setup a connection string with an ip address? I
tried server=xxx.xxx.xxx.xxx but it that couldn't connect from either server?
(I have both named pipes and tcp/ip enabled).
Could you point me to an article on how to do that, because if named pipes
are risky, we shouldn't be using them!
Thanks for your reply and help.
Cheers
Dan
"Charles Wang[MSFT]" wrote:

> Hi,
> To narrow down this issue, I have serveral questions which need your
> confirmation:
> 1. Could you ping the SQL Server successfully from the two clients?
> 2. What will happen if you explicitly add the two domain accounts to the
> users group of your third server ?
> Also, I recommend you try configuring the client settings as following:
> Click the Start menu;
> Type cliconfg.exe;
> Check if TCP/IP and Named Pipes protocols are enabled;
> Select the Alias tab;
> Check if the server instance exists in the list;
> If it's not existed, click the Add button;
> Input the alias name (<servername>\SQLEXPRESS) for Named Pipes protocol and
> click OK.
> Apply the settings, and then try again.
> If you want to use TCP/IP protocol, please first check the listening TCP
> port on your server via SQL Server Configuration Manager.
> Then add an alias for TCP/IP protocol at the clients. Note that you need to
> cancel the selection of "Dynamically determin the port" and input your TCP
> port number.
> An alias can be different with your SQL Server instance name, but the
> server name should be same as your server name.
> Please ensure that you can use telnet to access your SQL Server TCP port
> before configuring the alias for TCP/IP:
> telnet <your server name> <port number>
> If you have any other concerns, please feel free to let me know. It's my
> pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
|||Hi Dan,
Thanks for your updating and response.
The user error should be caused by the windows logon account at your client
computer not matching the Windows authentication on the server.
Please check if the Windows login account is a member of the Users or
Administrators group under your SQL Server machine. You can maunually add
it to the Users group and then add it to the logins of your SQL Server 2005
Express and assign it permissions in SQL Server 2005 Express.
If you did not install the SQL Server 2005 Management Studio Express, you
can download it from:
http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-
94B8-5A0F62BF7796&DisplayLang=en
This tool provides you an UI to manage your SQL Server 2005 Express like
SQL Server 2000 Enterprise Manager.
If this issue persists, it seems that it is related to the settings of your
web application. You can try using impersonation in your web.config file:
<system.web>
...
<authentication mode = "windows" />
...
<identity impersonate="true" />
...
</system.web>
For more information, you can refer to:
Using sql trusted Connections with asp.net
http://idunno.org/dotNet/trustedConnections.aspx
If you use impersonation, please ensure that the two Windows logon accounts
of your two web application servers must be as a member of the
Users/Administrators group on your SQL Server 2005 machine and be assigned
permissions in your SQL Server 2005 Express instance.
For the second error "Cannot connect to Server/Instance specified", it is
most likely a communication level error. If you are worried about the
security of Named Pipes, you can maually configure the TCP/IP protocol by
following my first reply. Also, you can configure a static TCP port at the
server side. By using SQL Server Configuration Manager, you can specify a
static TCP port under the IP All, and leave other fields under IP1 and IP2
blank:
1. Open SQL Server Configuration Manager;
2. Select Protocols for <instance name>;
3. Double click TCP/IP, switch to the IP Address tab;
4. Specify the TCP Port under the IPAll, leave all TCP Dynamics Ports under
IP1,IP2 and IPAll blank, and leave TCP Port under IP1 and IP2 blank.
5. Click OK and restart your SQL Server service.
On the client machines, ensure that you can use telnet to connect to the
server at the TCP port. Then try adding an alias for TCP/IP by using
cliconfg.exe at the client and try again.
Please feel free to let me know if you have any other questions or
concerns.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
|||Hi Dan,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support
sql

3 server scenario problem

Hi
I'm having trouble connecting to an instance of SQL Server 2005 (Express)
from 2 individual Windows Server 2003 machines running IIS (asp.net 2.0)
I have an IIS on each 2k3 machine, both with identical sites. The idea is
simply that we have a third sql server which is accessed by these 2 websites
.
One server gives me a Server login error (user not found:
MYDomain\ServerName), which the second server gives me the usual error 26 -
Cannot connect to Server/Instance specified.
Both IIS apps use the same query string (server=MYSQLSERVER\DARTS;
database=dartsV2;trustedconnection=yes;)
but as I say, one connects, one doesn't! very odd. Anyone got any
suggestions? I've enabled named pipes and tcp/ip on the sql server, along
with "allowing remote and local connections" using surface area config. I've
even setup SQL Browser, which seemed to cure one of the servers (both were
giving me the cannot find instance specified error).
Any suggestions guys?! I'm getting kinda desperate on this one... :o)
Cheers
DanHi,
To narrow down this issue, I have serveral questions which need your
confirmation:
1. Could you ping the SQL Server successfully from the two clients?
2. What will happen if you explicitly add the two domain accounts to the
users group of your third server ?
Also, I recommend you try configuring the client settings as following:
Click the Start menu;
Type cliconfg.exe;
Check if TCP/IP and Named Pipes protocols are enabled;
Select the Alias tab;
Check if the server instance exists in the list;
If it's not existed, click the Add button;
Input the alias name (<servername>\SQLEXPRESS) for Named Pipes protocol and
click OK.
Apply the settings, and then try again.
If you want to use TCP/IP protocol, please first check the listening TCP
port on your server via SQL Server Configuration Manager.
Then add an alias for TCP/IP protocol at the clients. Note that you need to
cancel the selection of "Dynamically determin the port" and input your TCP
port number.
An alias can be different with your SQL Server instance name, but the
server name should be same as your server name.
Please ensure that you can use telnet to access your SQL Server TCP port
before configuring the alias for TCP/IP:
telnet <your server name> <port number>
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Charles,
Thanks for the reply. I've actually fixed this particular issue, I simply
copied the code with the "user" error onto the other IIS server, so now I
have that error on both. However, I'll post somewhere else for that...
For clarification...
1) yes. I could also use osql (or sqlcmd) to connect to the sql server from
both domain servers, cos they both had sqle installed but no instance
running).
2) I tried that, but it couldn't find the computer accounts when I tried
adding roles etc - presumbly because they were computer accounts and not
users, I'm not sure though?
I have named pipes enabled but this is considered a security risk is it not?
However, I'm not sure how to setup a connection string with an ip address? I
tried server=xxx.xxx.xxx.xxx but it that couldn't connect from either server
?
(I have both named pipes and tcp/ip enabled).
Could you point me to an article on how to do that, because if named pipes
are risky, we shouldn't be using them!
Thanks for your reply and help.
Cheers
Dan
"Charles Wang[MSFT]" wrote:

> Hi,
> To narrow down this issue, I have serveral questions which need your
> confirmation:
> 1. Could you ping the SQL Server successfully from the two clients?
> 2. What will happen if you explicitly add the two domain accounts to the
> users group of your third server ?
> Also, I recommend you try configuring the client settings as following:
> Click the Start menu;
> Type cliconfg.exe;
> Check if TCP/IP and Named Pipes protocols are enabled;
> Select the Alias tab;
> Check if the server instance exists in the list;
> If it's not existed, click the Add button;
> Input the alias name (<servername>\SQLEXPRESS) for Named Pipes protocol an
d
> click OK.
> Apply the settings, and then try again.
> If you want to use TCP/IP protocol, please first check the listening TCP
> port on your server via SQL Server Configuration Manager.
> Then add an alias for TCP/IP protocol at the clients. Note that you need t
o
> cancel the selection of "Dynamically determin the port" and input your TCP
> port number.
> An alias can be different with your SQL Server instance name, but the
> server name should be same as your server name.
> Please ensure that you can use telnet to access your SQL Server TCP port
> before configuring the alias for TCP/IP:
> telnet <your server name> <port number>
> If you have any other concerns, please feel free to let me know. It's my
> pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>|||Hi Dan,
Thanks for your updating and response.
The user error should be caused by the windows logon account at your client
computer not matching the Windows authentication on the server.
Please check if the Windows login account is a member of the Users or
Administrators group under your SQL Server machine. You can maunually add
it to the Users group and then add it to the logins of your SQL Server 2005
Express and assign it permissions in SQL Server 2005 Express.
If you did not install the SQL Server 2005 Management Studio Express, you
can download it from:
http://www.microsoft.com/downloads/...A5AE-4BD1-4E3D-
94B8-5A0F62BF7796&DisplayLang=en
This tool provides you an UI to manage your SQL Server 2005 Express like
SQL Server 2000 Enterprise Manager.
If this issue persists, it seems that it is related to the settings of your
web application. You can try using impersonation in your web.config file:
<system.web>
..
<authentication mode = "windows" />
..
<identity impersonate="true" />
..
</system.web>
For more information, you can refer to:
Using sql trusted Connections with asp.net
http://idunno.org/dotNet/trustedConnections.aspx
If you use impersonation, please ensure that the two Windows logon accounts
of your two web application servers must be as a member of the
Users/Administrators group on your SQL Server 2005 machine and be assigned
permissions in your SQL Server 2005 Express instance.
For the second error "Cannot connect to Server/Instance specified", it is
most likely a communication level error. If you are worried about the
security of Named Pipes, you can maually configure the TCP/IP protocol by
following my first reply. Also, you can configure a static TCP port at the
server side. By using SQL Server Configuration Manager, you can specify a
static TCP port under the IP All, and leave other fields under IP1 and IP2
blank:
1. Open SQL Server Configuration Manager;
2. Select Protocols for <instance name>;
3. Double click TCP/IP, switch to the IP Address tab;
4. Specify the TCP Port under the IPAll, leave all TCP Dynamics Ports under
IP1,IP2 and IPAll blank, and leave TCP Port under IP1 and IP2 blank.
5. Click OK and restart your SQL Server service.
On the client machines, ensure that you can use telnet to connect to the
server at the TCP port. Then try adding an alias for TCP/IP by using
cliconfg.exe at the client and try again.
Please feel free to let me know if you have any other questions or
concerns.
Sincerely yours,
Charles Wang
Microsoft Online Community Support|||Hi Dan,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support

21776 Error by EM when a view column name is changed.

I am wondering if there is a sp or some other method to fix an issue that
arises when you change the name of a column in a view.
The scenario is:
1. Create the view
2. Alter the view, changing the name of one of the fields
3. Within EM, select the view | Properties | permissions, and then check the
column permissions - you get an error "21776 [SQL-DMO] The name
'<newcolumnname>' was not found in the columns collection..."
The BOL item on ALTER VIEW warns that if you have permissions defined on a
view column, and then change the name of the column, the permissions do not
follow, BUT, in addition to this, you cannot even see the NEW column to
assign the permissions again in the above scenario.
Has anyone run into this?
Thanks
Best regards,
BlairCheck out sp_refreshview in BooksOnLine.
Andrew J. Kelly SQL MVP
"BlairH" <BlairH@.discussions.microsoft.com> wrote in message
news:8AB571A3-779F-497A-A707-703B66FEF13E@.microsoft.com...
>I am wondering if there is a sp or some other method to fix an issue that
> arises when you change the name of a column in a view.
> The scenario is:
> 1. Create the view
> 2. Alter the view, changing the name of one of the fields
> 3. Within EM, select the view | Properties | permissions, and then check
> the
> column permissions - you get an error "21776 [SQL-DMO] The name
> '<newcolumnname>' was not found in the columns collection..."
> The BOL item on ALTER VIEW warns that if you have permissions defined on a
> view column, and then change the name of the column, the permissions do
> not
> follow, BUT, in addition to this, you cannot even see the NEW column to
> assign the permissions again in the above scenario.
> Has anyone run into this?
> Thanks
> --
> Best regards,
> Blair|||Thanks very much Andrew. That's exactly what I was looking for. In
addition, it apears that you have to restart EM in order for the fix to take
effect. (but it does)
Thanks again.
--
Best regards,
Blair
"Andrew J. Kelly" wrote:

> Check out sp_refreshview in BooksOnLine.
>
> --
> Andrew J. Kelly SQL MVP
>
> "BlairH" <BlairH@.discussions.microsoft.com> wrote in message
> news:8AB571A3-779F-497A-A707-703B66FEF13E@.microsoft.com...
>
>|||All you should have needed to do after issuing sp_refreshview was to refresh
that node in EM. But restarting works too<g>.
Andrew J. Kelly SQL MVP
"BlairH" <BlairH@.discussions.microsoft.com> wrote in message
news:5EF6E77B-79A3-46AB-8070-3842A950407F@.microsoft.com...[vbcol=seagreen]
> Thanks very much Andrew. That's exactly what I was looking for. In
> addition, it apears that you have to restart EM in order for the fix to
> take
> effect. (but it does)
> Thanks again.
> --
> Best regards,
> Blair
>
> "Andrew J. Kelly" wrote:
>

2005 Maintenance Plan or Security bug?

Curious if this scenario is by design or a possible bug. As recommended by MS best practices for SQL 2000 we always removed the BUILTIN\Administrators login. After doing the same on our 2005 installations it appears to have caused errors in the edit\view functionality in the Maintenance Plans and jobs. One example is to open a plan in modify mode and then select the logging button. - 'Unhandled exception has occured in a component in your application' (have debug file)

Another error occurs when attempting to edit a step of the Maintenance Plan job. Select the subplan step > edit > select any tab other than General and this error occurs.

TITLE: SSIS Execution Properties

The LoadFromSQLServer method has encountered OLE DB error code 0x80040E4D (Login failed for user 'domain\xxxxx'.). The SQL statement that was issued has failed.

I re-added the BUILTIN\Administrator to one 2005 instance and it corrected the errors completely. We are running Enterprise edition on W2K3 SP1 server using mixed mode auth. I do have Admin rights to the servers so it appears that windows security is being used for some maintenance plan functionality rather than the sql security. The errors could be reproduced running the Management Studio locally or remote connected as sa.

Any insight to this weirdness would be appreciated.

Allison

Check that you SQL Services have explicit access granted and aren't going through BUILTIN admins

|||Not sure exactly what you mean by 'have explicit access' but I did add the SQL Services start up account (domain account with server admin rights) as a sysadmin and it did not correct the problem.

All jobs execute with out the start up account having an explicit sql login.

Allison|||The (Login failed for user 'domain\xxxxx'.) is this a literal posting or have you blocked out the name of the account the SQL agent uses?
|||Might be that something in your plan accesses a location that doesn't exist or no permissions a granted for the user opening the plan...there should be useful information in the paramters passed to the LoadFromSQLServer method that could resolve this.
|||Yes I did block it out of the message but it was not the SQL Services account in the error message it was my user account. So what you are saying is that I can create a maintenance plan but when I attempt to edit it my credentials, not the service credentials are being checked?

Not sure hove to look at the parameters for LoadFromSQLServer.

Thanks
A|||Yes, when you automate a job it will run under a specified or agent account. When you are editing it, your account is being used to access the objects involved.