I opened the port for SQL Server 1433, and MSDE port 1503(get from registry) in the Windows Firewall, and enable DISABLENETWORKPROTOCOLS=0 for MSDE.
I can connect to SQL Server and MSDE in the local server, and can connect to the SQL Server in another machine(ClientA) from Visual Studio .Net and SQL Sever authentication. but can't connect to the MSDE from another machine(ClientA) in Visual Studio .Net by SQL Server Authentication. The visual studio .net can't detect the MSDE instance automatically.
BTW, both the server and ClientA are in the same Workgroug, no domain defined.
I think, this should be something about configuration. Any clue? Thanks very much.
If you're using local security on each machine (because you're in a workgroup) then Windows security probably won't work the way you think it is working. In a domain called DomainA, the account UserA is known as DomainA\UserA. Since all the servers joined to that domain see the same security, they all view this person as DomainA\UserA. Since you don't have a domain, each server has it's own security, meaning that an account created and named on each machine as UserA would be known as ServerA\UserA and ServerB\UserA. These are two different accounts.
So the point is that when SQL Server on ServerA is set to use Integrated security, the only accounts it knows about are ServerA, not Server B. When you log on to a system as ServerB\UserA, the SQL Server does not know who you are.
Now, since you are stating that you are using mixed authentication, you shouldn't have the problem. In fact, you state that you can log on using a tool other than SQL Server from any system in the workgroup, as long as you use a SQL Server account. So this leads me to think that your connection string is trying to use a Windows account to connect. That's where you should start looking.
|||Ok, I have spent the last 3 days trying to do research on SQL Server 2K5 Express connectivity and have not found any definite answers as to what I need to do to solve the problem.
Background:
Client: WinXP Pro 2002 SP2 running MS SQL Server Management Studio Express
Server: WinXP Pro 2002 SP2 running MS SQL Server 2005 Express configured for mixed authentication.
Both machines not on domains but share a common workgroup.
Windows firewalls on these machines disabled. Router firewall opened port 1433 to server. I am connecting from Client to Server by hitting the router IP and coming back in through port forwarding since my clients will have to be hitting my server through my router. Connectivity of the 2 machines is fine as I can telnet from one to the other on 1433 without issue when first stopping SQL Server of course.
Requirement: Connecting the two using Windows Authentication and TCP protocol to MS SQL Server. Ultimately, I am doing this to have clients connect into my server withoug having to be part of my domain/workgroup.
Server name in studio set to: tcp:www.myserver.org\sqlinstance,1433
Login failed for user ''. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
Now I have read some articles implying that this should be possible using 'workgroup authentication' so I tried that - basically ensuring that identical accounts are set up on both machines with the same passwords - I know this is working since I can transparently (no login required) map shares from one to other. This however still left me with the above error. I am assuming mapped drives used named pipes so this doesn't prove much when I am using Studio to connect via TCP.
For later reference, lets say the client machine is named CLIENT and the server is named SERVER and the common username is USERNAME and common password of PASSWORD.
I read an article stipulating that the authentication is domain based so that machine SERVER would receive an authentication SID (terminology may be wrong here) of CLIENT\USERNAME but since neither machine is on a domain SERVER could not validate the credentials on CLIENT and so it cannot work.
But yet another article said it can...go into Start|Settings|Control Panel|User Accounts and make click on 'Manage my network password' and make a new entry for the server and enter the username as it is required on the server...so I created one as follows:
Server: SERVER
User Name: USERNAME
Password: PASSWORD
...but received the same error.
If I use SQL Server Authentication it works fine...but my readings have shown that SQL Server Authentication is not all that secure (but in fairness don't know if most of the articles I read this in were talking about previous versions of MS SQL Server 2005).
So my questions:
1) Is SQL Server Authentication in MS SQL Server 2005 secure (maybe not as secure as Windows Auth, but secure enough to trust sensitive information to?)
2) Is it possible to use Windows Authentication between 2 machines separated by the internet and not part of domains? If so, what are the requirements for this? Common accounts/passwords? Common Workgroup? etc...
3) If 2) isn't possible does that imply that I need an OS that can act as a domain controller just to host my SQL Server? If so, could I then 'Manage my network password' on any client that needs to connect and have it provide correct domain\user info to the domain controller thereby avoiding having to join the domain to connect to the SQL Server - keep in mind the ultimate goal here is to have clients connect into my SQL Server via the internet where they won't be part of my domain and/or possibly can not be made part of my workgroup.
Thanks so much for any help you can provide.
Best Regards,
DOD
|||Good Questions - Hopefully I can help clear these up a little.
1. SQL Auth is very secure. In SQL 2K5, if you're using Win 2K3, you can even tie some of the Win2K3 policies (password change, length, etc.) to SQL Server Accounts. The rest of these answers is only for Windows Auth. For SQL Server Auth you can just connect using the name and account pair.
2. Management Studio uses your current account to connect - that's by design. If your account can be seen/trusted by the other system, you're in. If not, right-click Management Studio and select "Run As" if you're using XP. Then you can provide a domain/user pair.
You can definitely connect without common domains through any application by providing a connection string. You can also do this with any of the command-line utility such as sqlcmd, since you provide a connection string. Books Online has more about sqlcmd.
3. Not at all. Windows Security is a big subject, but the point is that you just need to determine what the server needs to see. I run several SQL Servers with Local (Workgroup) security.
- Buck
|||Thank you SOOOO much...it's nice to have an authority on the matter give a 'matter of fact' response!
Just a couple more questions regarding the above...
1) Given that I will not be running Win2K3 in the shortterm, but hosting SQL Server on a WinXP 2002 SP2 box, is SQL Auth in SQL Server 2K5 is inherently secure enough for sensitive business information when not tying into OS security policy functionality?
2) Is there any way to have an app always ran under different credentials (to avoid having to select Run As from the context menu each time) via some shortcut property, registry modification, or something else?
Once again, thanks in advance...I spent literally days researching this but because of my security concerns hadn't found what I considered a consistent and direct answer to the above.
<edited>
I just tried using the Run As and get the same error:
Login failed for user ''. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
Interesting that in the username field it still shows my local workgroup\logged in user versus what I set up in the Run As dialog. Is this just a bug in that the GUI does not reflect the Run As parameters or is it in fact just always using the logged in user?
I can connect using SQL Auth, so know without question that firewall settings are not an issue. I specified the user as:
WXP-P2000\myadminuser where WXP-P2000 is the computer name of the machine SQL Server is running on and myadminuser is a user in the administrators group on that machine. I provided the password for that account (I know that it is the correct password as I use RDP to connect to that machine to play remotely on the SQL side as needed)
My connection string in MS SQL Server Management Studio Express for 'Server name:' is set to:
tcp:mysql.myip.com\mysqlinstancename,1433 and know this is working since I can use SQL Auth and connect without issue.
From what I understand the above is an auth issue and not a SQL login issue, but what else can I try?
</edited>
Best Regards.
|||No, it's not a GUI issue - that's an odd form of that error, though. It should have had a user name variable. Which OS are you on?
1. Yes - as long as you follow basic security checklists you should be fine. There are lots of security resources for SQL 2K5, just search for those. Basically it is applying service packs, no blank passwords, don't enable bad things, and so on. The Best Practices Analyzer (BPA) will also check some security basics for you.
2. That I have not done. Are you trying to run SSMS as an application? I would recommend managing the system uing RDP and only hit the database through applications - that's the normal management profile.
|||Thanks for the quick reply.
Today I tested from a different machine at my company versus from a machine at home.
All machines involved have been WinXP 2002 SP2 boxes.
1. Will do, I have implemented a good security approach that I acquired doing my last 4 days of research.
2. I am running it as a remote app, trying to connect from my company back to my test SQL Server 2K5 Express install back home. I right click on the 'SQL Server Management Studio Express' shortcut under Start|Programs|Microsoft SQL Server 2005, choose 'Run As', type in the info and it starts up and the rest is as I've described - User name field in studio has my local machine name\user name and not what I've supplied. Ultimately, I can live with managing it via RDP, but can't see any reason why remote management would not work. Wouldn't remote management be more efficient as well as now it is just an app sending/receiving data versus the RDP session having to maintain sending/receiving screen updates (even if I reduce what is updated - i.e. Desktop background, themes, etc)?
Once again thank you for your assitance.
Best Regards,
DOD
|||
On number two, there are lots of arguments on both sides. RDP is actually pretty light from a traffic persepctive, especially if you follow a best-practice for setting no themes on servers and using a plain black background. For truly light management, use PowerShell or sqlcmd.
- Buck
|||Don't mean to belabor the point, but do you have any other suggestions regarding connecting remotely into MS SQL Server 2K5 Express. Is there anything I could have missed in the blog trail above?
The most suspect thing to me is the fact that when I start up Studio using the Run As as discussed, it doesn't show me the computer\user that I input but rather the locally logged in credentials...have you seen this before in the workgroup auth you have used successfully in the past? Seems to me that if it is still using the logged in creds, the error would be expected...if this is the case, what's up with the Run As command or the support of it in Studio?
Cheers.
No comments:
Post a Comment