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. 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. 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. I'll have a peek at the pymssql and TDS docs tonight as well. Thanks for the help. Bill. _______________________________________________ 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]
