Bill Witherspoon wrote: > Carl Karsten wrote: >> Bill Witherspoon wrote: >>> Carl Karsten wrote: >>>> Bill Witherspoon wrote: >>>>> Carl Karsten wrote: >>>>>> Bill Witherspoon wrote: >>>>>>> Carl Karsten wrote: >>>>>>>> Bill Witherspoon wrote: >>>>>>>>> Carl Karsten wrote: >>>>>>>>>> Ed Leafe wrote: >>>>>>>>>>> On Jun 13, 2007, at 2:16 PM, Carl Karsten wrote: >>>>>>>>>>> >>>>>>>>>>>> -> We're using >> Windows >> Authentication currently, >>>>>>>>>>>> >>>>>>>>>>>> I think that is the 'problem.' >>>>>>>>>>> Why? All that means is that SqlServer will authenticate the >>>>>>>>>>> username/ >>>>>>>>>>> pw through Windows authentication. If the username/pw is legit, it >>>>>>>>>> When using win, you don't need to pass a user/pw, and I think you >>>>>>>>>> need to tell >>>>>>>>>> it which method to use. so if anything, passing a user/pw may be >>>>>>>>>> the way to >>>>>>>>>> tell it to use SQL auth. >>>>>>>>>> >>>>>>>>>>> should be fine. In fact, when I helped test pymssql for Dabo, I >>>>>>>>>>> used >>>>>>>>>>> Windows authentication to my SQL Server. >>>>>>>>>> Was your server set to win-only or mixed? >>>>>>>>>> >>>>>>>>>> Carl K >>>>>>>>>> >>>>>>>>> Carl, >>>>>>>>> It's set to mixed. Let me try to remove the user/pass right now. >>>>>>>> If it is set to mixed, then I would try to use SQL, not win. >>>>>>>> >>>>>>>>> Well, that's weird - if you leave the user/pass parameters it looks >>>>>>>>> like >>>>>>>>> it defaults to user = "sa". No go there either. >>>>>>>> yeah, I think there is a parameter you need to pass that explicitly >>>>>>>> tells it to >>>>>>>> use Win. >>>>>>>> >>>>>>>>> I also just tried user='billw' with no password, and password=''. No >>>>>>>>> go. >>>>>>>> Is that a user/pw that the server knows about, or a win user? >>>>>>>> >>>>>>>> If you /join #dabo on irc.freenode.net I'll setup a server setup you >>>>>>>> can hit. >>>>>>>> >>>>>>>> Carl K >>>>>>>> >>>>>>>> >>>>>>> Thanks Carl, >>>>>>> That's a generous offer - I'll take you up on it if it comes to that! >>>>>> I am around tonight and tomorrow morning. some time in the afternoon I >>>>>> will be >>>>>> out. it isn't that generous - I just need to tweak my firewall.conf, >>>>>> restart, >>>>>> give you a user/pw, wait for the test and tweak it back when we are >>>>>> done. that >>>>>> explanation probably just doubled the amount of work :) >>>>>> >>>>>>> Since I last posted, I tried changing the SQLDB to Windows >>>>>>> Authentication only from Mixed. That didn't seem to help. >>>>>> flip it back, make sure you know what your SA pw is and test with that. >>>>>> >>>>>>> The user/pass I'm using is my own domain user/pass (it's not a local >>>>>>> machine user/pass if that's what you mean). As for the server knowing >>>>>>> about it, I guess I assumed that it was querying our Domain Controller >>>>>>> behind the scenes? >>>>>> I don't think it is that 'simple', where the use of simple is being >>>>>> stretched... >>>>>> >>>>>> win auth is something like: log into your work station using a user/pw >>>>>> that gets >>>>>> authenticated somewhere, somehow, and then when you try to connect to >>>>>> the sql >>>>>> server it uses a token (or something your work station was given) to >>>>>> identify >>>>>> yourself to sql. I don't think sql ever sends your user/pw anywhere, >>>>>> cuz I >>>>>> don't think it ever gets it. >>>>>> >>>>>>> I've resisted trying to set up SQL authentication because I didn't want >>>>>>> the headache of managing another set of users in addition to our AD >>>>>> SQL auto is simple: you setup a user/pw on the server, and you use that >>>>>> user/pw >>>>>> when you log in. I have never had to deal with enough users for it to >>>>>> be a >>>>>> headache. >>>>>> >>>>> Carl, >>>>> >>>>> I did get this working using SQL auth. I can return records from my >>>>> database using pymssql manually. So finally, I think we can eliminate >>>>> the SQL Server side of the equation. >>>> Using what code? >>>> >>>> Is the server now set to Win or Mixed? >>>> >>>>> Unfortunately, the connection dialog still returns "Unable to Connect". >>>> My guess is seeing the code you used will bring to light a problem with >>>> the dabo >>>> connection stuff. >>>> >>>>> When I check the .cnxml file I noticed that it's hashing the password. >>>>> That's good for obvious reasons - but it's the only thing I see >>>>> different than my hand coded connection string (I'm passing the password >>>>> in plain text). >>>> huh? passing the pw doesn't sound like windows auth. >>>> >>>>> I did try hand editing the .cnxml file, but that didn't work. The >>>>> connection dialog doesn't seem to like the file anymore. >>>>> >>>>> Any more ideas? (I would suggest Postgres, but the decision to use >>>>> SqlServer was corporate). >>>> suggestion: slow down :) >>>> >>>> here is the plan: >>>> 1: connect using just a few python commands (I think you have done that) >>>> 2: connect using just a few simple dabo calls (no gui things) >>>> 3: connect using dabo calls that are similar to how dabo's cd drives things >>>> 4: eventually use the CD. >>>> >>>> And show your work after each step :) >>>> >>>> Also, are you a VFP guy? if so, we may try a few VFP lines. >>>> >>> Nope - not a VFP guy (took me a minute to figure out what that is). More >>> of a Python/WxPython-have-to-pretend-to-know-Windows-at-work-but-really >>> -run-linux-at-home-cause-I-can't-afford-Mac ;-) >> No prob. another thing to play with is osql.exe - the MsSql command line >> client. That should give you a good idea of what you can/can't do. >> try this: >> c:\>osql -S 192.168.51.26 -E >> >> you should get: >> 1> >> >> type "exit" enter to exit. >> >> also try it with >> c:\>osql -S 192.168.51.26 -U plant_user -P emseal01 >> >> May or may not work, depending on 'suff' :) >> >>> The python code that works is what Ed suggested earlier: >>> >>> import pymssql >>> conn = pymssql.connect(host="192.168.51.26", user='plant_user', >>> password="emseal01", database="testing") >> According to the docs, that is not using what I think we are calling win >> auth: >> >> Found some info: >> http://www.freetds.org/userguide/domains.htm >> """ >> Domain logins can be used only with TDS protocol versions 7.0 and 8.0. >> >> As mentioned in the installation chapter, Microsoft SQL Server includes the >> ability to use domain logins instead of standard server logins. The >> advantage of >> doing this is that the passwords are encrypted on the wire using a >> challenge-response protocol. FreeTDS began supporting domain logins in >> version 0.60. >> >> To use domain logins, use the 'DOMAIN\username' syntax for the username and >> use >> the domain password. >> >> When FreeTDS sees the "\" character, it automatically chooses a domain login. >> """ >> >> So it looks like you need to pass 'something' with a \ in it. Personally, I >> would try it with just a \: (which needs to be escaped, so use 2) >> >> import pymssql >> conn = pymssql.connect(host="192.168.51.26", user='\\', database="testing") >> >> (you will either get a >>> or a big error- no need to run queries) >> >> for kicks (cuz the docs may be wrong) try >> conn = pymssql.connect(host="192.168.51.26", database="testing") >> >>> I've switched the SQL box back to mixed mode, and added the SQL user >>> noted in the code above. This works and returns records from the >>> po_master table. >> Why did you do that? you are going backwards :) >> >> Switch it back to Win and try the above line. report the results of both >> tries. >> make sure sa is the only user on the SQL server. >> >> Even if you can connect osql.exe witout a user, I think you need to pass >> something to pymssql.connect( cuz if you look at the code (below) you will >> see >> that if you don't pass a user,pw, it uses 'sa','' >> >>> When I drop the same user/pass into the connection dialog it doesn't >>> work. That's when I noticed that the password is being hashed in the >>> .cnxml file. >>> >>> I though maybe I should just give up on following the tutorial like you >>> suggest, and just dive into some code - but it seemed like a good way to >>> get started, plus I thought I could save some grunt work by leveraging >>> the tools. >> yeah, slow... d o w n.... I'm not convinced you are doing Win auth yet. >> >> personally, I think this is enough of a headache to just use SQL auth. but >> I'll >> keep playing as long as you are :) >> >> Carl K >> > > Carl, > > The only thing I've gotten to work so far is SQL Auth (using the > user/pass above which definitely isn't a Windows domain user). The only > reason I tried SQL Auth was to see if I could get anything working - > like you say, it's a step backward and I would prefer not to go that way. >
OK, thats good to know. I kinda thought so. > Your osql.exe lines all work provided I've got Mixed Mode turned on > (with the user/pass above-makes sense). Turning off Mixed Mode and > trying to use my domain user/pass results in the same failure messages > as below. Trying it with no user/pass results in a request to use a > user/pass. If this doesn't work: c:\>osql -S 192.168.51.26 -E That means your windows login does not have rights to use the SQL server, and so you will never get win auth to work in python. > > None of the suggested pymssql lines work though. > For example for user="\\:" it returns: Login failed for user '\:'. > For user="\\", it returns Login failed for user '\'. I've also tried > every conceivable option of "Domain\User", "Domain\\User", > "[EMAIL PROTECTED]", etc. No surprise, given the osql -E didn't work. Once you get it working, then you will get to try these again :) Carl K _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users Searchable Archives: http://leafe.com/archives/search/dabo-users This message: http://leafe.com/archives/byMID/dabo-users/[EMAIL PROTECTED]
